DMW logo
(t) +44 (0)1732 833085
(e) david@consultdmw.com

How to Use Excel's COUNT, COUNTBLANK and COUNTA Functions

When you’re using Excel’s Count functions in your formulas, it’s important to understand how they respond to the content of cells that they’re addressing.

Last updated on 2021-08-30 by David Wallis.


Appearances Can Be Deceptive

A casual inspection of this spreadsheet might lead us to mistaken conclusions about the information conveyed:

Excel spreadsheet

For instance, we might conclude that the shaded cells for Beetroot Fat and in the Sugar column indicate that values are missing and yet to be input.

But there are other reasons for for supposedly “missing” Sugar and Fat values:

Please bear all such possibilities in mind when using Count functions in your formulas. And, be alert for any other possibilities once you spreadsheet gets into other people’s hands.

Additionally, note that, in Excel parlance, a cell that contains nothing at all is said to be “blank”.

The five Fat and Sugar cells may or may not be blank. We cannot know simply by observation. Now to understand what Count functions make of them.


The COUNT Function

Excel’s COUNT function returns the number of cells in a range that contain a number.

There are 66 cells in the range A1 to F11. Of these, 45 appear to contain numbers. So what does COUNT make of things?

Excel formula using COUNT function

All good: the result of the formula confirms our expectation based upon appearances. As the spreadsheet is updated, the Count follows any change to input values:

Excel COUNT function ignoring blanks

And again we’re happy, even if we disagree with the author of the spreadsheet that beetroot contains no fat:

Excel COUNT function counts zeros

However, if in the spreadsheet all is not entirely as it appears:

Excel COUNT formula appears incorrect

Even though it appears that there are four missing values, the Count formula is not fooled by the spreadsheet’s author applying to Cell C7 the same colour for font and background. The formula bar indicates that the number input into Cell C7 is zero.

In this example the author has been at their most wrong headed:

Excel numbers input as text

The value of Crouton Sugar has been input as text by typing '4.4, instead of 4.4 unadorned by the leading apostrophe.


The COUNTBLANK Function

The COUNTBLANK function counts the number of cells in a range that contain no content at all, i.e. are blank:

Excel COUNTBLANK function

If the user types a space into Cell E3, the function treats the cell as being non-blank, i.e. not empty:

COUNTBLANK function ignores spaces

The two zeros are non-blank values, COUNTBLANK ignoring them:

Zero values not counted by COUNTBLANK


The COUNTA Function

The COUNTA function counts the number of cells in a range that that aren’t blank:

Excel COUNTA function

I’d be grateful to COUNTA for raising my suspicions that there’s something amiss with this spreadsheet:

COUNTA counts spaces as content

Actually, the three apparently empty cells being filled with spaces. I’ve seen this in a number of spreadsheets, of which the majority were being updated by users who were unaware that the way to clear a cell is to use the Delete key.


Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

“… However, not everything that can be counted counts, and not everything that counts can be counted.”

William Bruce Cameron, Informal Sociology:
A Casual Introduction to Sociological Thinking

(1963)