Quick, clean, and to the point

Count cells that are not blank

Excel formula: Count cells that are not blank
Generic formula 

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 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.


To count non-blank cells with the COUNTIF function, you can use a formula like this:


This same approach can be extended with the COUNTIFS function like this:


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:


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:

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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.