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 2022-08-29 by David Wallis.
Appearances Can Be Deceptive
A casual inspection of this spreadsheet might lead us to mistaken conclusions about the information conveyed:
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:
- A missing value is meant to indicate a value of zero
- A user has typed a space instead of deleting a cell’s content
- The same colour has been applied to both font and cell background
- An inappropriate number format or conditional format has been applied.
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?
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:
And again we’re happy, even if we disagree with the author of the spreadsheet that beetroot contains no fat:
However, if in the spreadsheet all is not entirely as it appears:
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:
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:
If the user types a space into Cell E3, the function treats the cell as being non-blank, i.e. not empty:
The two zeros are non-blank values, COUNTBLANK ignoring them:
The COUNTA Function
The COUNTA function counts the number of cells in a range that that aren’t blank:
I’d be grateful to COUNTA for raising my suspicions that there’s something amiss with this spreadsheet:
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.
Applying Criteria to Counts
If you need to count values that match certain criteria, then Excel has the COUNTIF and COUNTIFS functions for you to use. Follow this link for details of these.
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) —