Excel formula: Count cells that are not blank

Generic formula

=COUNTA(range)

Explanation

To count the number of cells that are not blank in a range, you can use the COUNTA function. In the example shown, E5 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 empty strings returned by formulas (""). If you run into this problem, you can try 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.

Author

Dave Bruns

Related formulas

Count cells that contain numbers

To count the number of cells that are not blank, use the COUNT function. In the generic form of the formula (above) rng represents a range of cells. In the example, the active cell contains this formula: = COUNT ( B4:B8 ) How this formula works The...

Count cells that contain text

To count the number of cells that contain text (i.e. not numbers, not errors, not blank), use the COUNTIF function and a wildcard . In the generic form of the formula (above), rng is a range of cells, and "*" is a wildcard matching any number of...

Related functions

Excel COUNTA Function

The Excel COUNTA function returns the count of cells that contain numbers, text, logical values, error values, and empty text (""). COUNTA does not count empty cells.

Excel COUNTBLANK Function

The Excel COUNTBLANK function returns a count of empty cells in a range. Cells that contain text, numbers, errors, etc. are not counted. Formulas that return empty text are counted.

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.