# Count cells that are not blank

=COUNTA(range)

To count cells that are not blank in a range, you can use the COUNTA function. In the example shown, D5 contains this formula:

=COUNTA(B5:B9)

COUNTA is fully automatic. When given a range of cells, it returns a count of cells that contain numbers, text, logical values, and errors. Empty cells are ignored.

### With COUNTIF and COUNTIFS

To count non-blank cells with the COUNTIF function, you can use a formula like this:

=COUNTIF(range,"<>")

This same approach can be extended with the COUNTIFS function like this:

=COUNTIFS(rng1,">100",rng2,"<>")

Here we counting cells when the value in **rng1** is greater than 100 __and__ **rng2** is not blank.

See also: 50 examples of formula criteria.

### Count cells with at least one character

One problem with COUNTA is that it will also count empty strings returned by formulas (=""). If you run into this problem, you can use a formula like this:

=SUMPRODUCT(--(LEN(A1:A100)>0))

Here, the LEN function returns a character count for each cell in the range, which is then compared to zero with the greater than operator (>). This expression returns TRUE for cells that contain at least 1 character, and FALSE for others. The double-negative (--) is used to coerce the TRUE/FALSE values to ones and zeros, and the SUMPRODUCT function returns the sum.

### Count blank cells

To count cells that *are blank*, you can use the COUNTBLANK function like so:

=COUNTBLANK(B4:B9)

## Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

## Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.