COUNT, COUNTA and COUNTBLANK: which one counts what
COUNT counts numbers, COUNTA counts anything that is not empty, COUNTBLANK counts empty cells. Where each one surprises you, with one small sheet.
COUNT counts the cells that hold a number. COUNTA counts every cell that is not empty. COUNTBLANK counts the empty cells. The trouble is that "empty" and "number" do not always mean what you see.
This article follows one sheet. Every Monday, 8 sales reps type last week's units into a shared
file. The manager wants to know who has reported and who has not. Column C copies column B with
=IF(B2="","",B2), so an empty row does not show 0.
| Row | A | B | C |
|---|---|---|---|
| 1 | Rep | Units | Report |
| 2 | Amara | 14 | 14 |
| 3 | Ben | 0 | 0 |
| 4 | Chloe | on leave | on leave |
| 5 | Dev | ||
| 6 | Erin | 22 | 22 |
| 7 | Farid | ||
| 8 | Grace | 9 | 9 |
| 9 | Hugo |
B5 and B9 are truly empty. B7 looks empty, but Farid typed a space into it. Keep that in mind.
The three counts on column B
=COUNT(B2:B9) gives 4. That is 14, 0, 22 and 9, the cells with
a number.
=COUNTA(B2:B9) gives 6. It adds Chloe's "on leave" and Farid's
space.
=COUNTBLANK(B2:B9) gives 2. That is Dev and Hugo.
So which answer is "how many have reported"? It depends on the question. Four reps sent a number. Six cells have something in them. Two reps sent nothing at all, as far as COUNTBLANK can tell.
0 is a number, and COUNT counts it
Ben sold nothing, but he did report. COUNT includes his 0, which is right. A report of 0 is not the same as no report.
If you want the reps who sold something, that is a different question. Use COUNTIF with a
condition. =COUNTIF(B2:B9,">0") gives 3.
AVERAGE follows the same rule as COUNT. =AVERAGE(B2:B9) gives
11.25. That is the total of 45, divided by the 4 numbers. Ben's 0 pulls the average down. The
empty cells and "on leave" do not count at all.
Words are invisible to COUNT
COUNT skips "on leave", because it is text. COUNTA counts it. So the gap between the two is the number of cells that hold something other than a number.
=COUNTA(B2:B9)-COUNT(B2:B9) gives 2.
That is one more than you expect. Chloe is one. The other is Farid's space.
Run this check on any column that should hold only numbers. If it is not 0, some cells hold text. A number stored as text is one common cause. It looks like a number, and COUNT skips it too.
A space is not empty
B7 looks empty on screen. COUNTA counts it, and COUNTBLANK does not. So Farid is counted as reported, and he is not on the "missing" list either.
=LEN(B7) gives 1. An empty cell gives 0. That one character is
the space.
To find spaces in a column, =COUNTIF(B2:B9," ") gives 1. Select
B7 and press Delete to make it truly empty.
This happens more than you think. People "clear" a cell by typing a space over the old value.
A formula that shows nothing is counted twice
Now look at column C. It shows the same values as column B. But every cell in it holds a formula, and that changes the counts.
=COUNTA(C2:C9) gives 8. Every cell has a formula, so every cell
is "not empty", even C5 and C9.
=COUNTBLANK(C2:C9) gives 2. COUNTBLANK treats the empty text
"" as empty.
So C5 and C9 are counted by both functions. COUNTA plus COUNTBLANK gives 10, on a range of 8 cells. On column B the same sum gives 8, as you would expect.
What goes wrong: someone builds a "how many reported" cell with COUNTA on a formula column. It says 8 of 8 every week, whatever the reps do. Nobody notices, because 8 looks like good news.
On a formula column, count what you want directly.
=COUNT(C2:C9) still gives 4, because it counts only numbers. For
a word, use COUNTIF. =COUNTIF(C2:C9,"on leave") counts the rep on
leave.
What each function counts
Here is the full list, for the cells you meet on real sheets.
| The cell holds | COUNT | COUNTA | COUNTBLANK |
|---|---|---|---|
| A number, such as 14 | Yes | Yes | No |
| 0 | Yes | Yes | No |
| A date | Yes | Yes | No |
| Text, such as on leave | No | Yes | No |
| A number stored as text | No | Yes | No |
| TRUE or FALSE | No | Yes | No |
| An error, such as #N/A | No | Yes | No |
| A single space | No | Yes | No |
| A formula that shows "" | No | Yes | Yes |
| A truly empty cell | No | No | Yes |
Dates count as numbers, because a date in Excel is a number. The dates article explains why.
COUNT and COUNTA can take several ranges, such as
=COUNT(B2:B9,D2:D9). COUNTBLANK takes only one range.
Which one to use
- The column should hold numbers: COUNT. It tells you how many real numbers there are.
- The column holds names or words: COUNTA. COUNT on a list of names gives 0.
- You want what is missing: COUNTBLANK, on typed values. On a formula column, count the values you want instead.
When the numbers disagree, trust the disagreement. It means the column holds something you did not expect. Find that cell before you send the report.
For counting with conditions, the COUNTIF practice article has eight exercises. The lessons below practise COUNT, COUNTA, COUNTBLANK and COUNTIF. Each lesson is a real sheet you type into, and each answer is checked when you press Enter.