Exceljet

Quick, clean, and to the point

Count cells that are not blank

Excel formula: Count cells that are not blank
Generic formula 
=COUNTA(range)
Explanation 

To count of cells that are not blank in a range, you can use the COUNTA function. In the example shown, E5 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 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 things when the value in rng1 is greater than zero 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 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:B8)
Author 
Dave Bruns

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.