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.

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)