Count cells that are not blank

Count cells that are not blank

Generic formula 

=COUNTA(rng)

Related formulas 

Count cells that contain numbers

Count cells that contain text

Explanation

To count the number of cells are not blank, use the COUNTA function. In the generic form of the formula (above) rng represents a range of cells.

In the example, the active cell contains this formula:

=COUNTA(B4:B8)

How this formula works

COUNTA is fully automatic. When given a range of cells, it counts cells that contain numbers, text, logical values, and errors. COUNTA does not count empty cells. 

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

=COUNTBLANK(B4:B8)

Count cells with at least one character

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

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

The LEN function returns a character count, so SUMPRODUCT will count only cells that contain least 1 character. The double-negative coerces TRUE/FALSE values to ones and zeros.

Count cells that are not blank

Generic formula 

=COUNTA(rng)

Related formulas 

Count cells that contain numbers

Count cells that contain text

Explanation

To count the number of cells are not blank, use the COUNTA function. In the generic form of the formula (above) rng represents a range of cells.

In the example, the active cell contains this formula:

=COUNTA(B4:B8)

How this formula works

COUNTA is fully automatic. When given a range of cells, it counts cells that contain numbers, text, logical values, and errors. COUNTA does not count empty cells. 

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

=COUNTBLANK(B4:B8)

Count cells with at least one character

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

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

The LEN function returns a character count, so SUMPRODUCT will count only cells that contain least 1 character. The double-negative coerces TRUE/FALSE values to ones and zeros.