Count cells that are not blank

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)

How this formula works

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)