Excel has three functions that all count cells, and yet they almost never return the same number. COUNT, COUNTA and COUNTBLANK look the same, but each one counts a different kind of content. Choosing the wrong one is one of the most common reasons a total does not match reality.
Try it right now
Here is a situation every Excel user meets one day: an attendance list filled in by several people. Some typed Yes or No, others typed 1 or 0, and a few left the cell empty. Diagnose this list in 8 steps, free and corrected as you type:
COUNT counts numbers only
=COUNT(C2:C16)
COUNT counts the cells that contain a number: whole numbers, decimals, and also dates, because Excel stores dates as numbers. It ignores text, empty cells, TRUE/FALSE and errors.
On the attendance list, COUNT returns 5, not 15. Only five people were recorded with a number.
💡 That is why COUNT is the fastest way to check that a column is really numeric. If COUNT returns less than the number of rows, some values are text, and every SUM built on that column is already wrong.
COUNTA counts everything that is not empty
=COUNTA(C2:C16)
The A stands for All. COUNTA counts every cell that contains something: numbers, text, dates, TRUE/FALSE and even errors. Only a truly empty cell is left out.
⚠️ The trap: a formula that returns an empty text, like =IF(A2>10,”OK”,””), looks empty on screen. But the cell contains a formula, so COUNTA counts it.
COUNTBLANK counts what is missing
=COUNTBLANK(C2:C16)
COUNTBLANK does the opposite: it counts the empty cells. Use it to check a form, an import or a survey: how many answers are still missing?
And here is the contradiction: COUNTBLANK also counts a cell with an empty text “” returned by a formula, while COUNTA counts that same cell as filled. On a column of formulas, COUNTA + COUNTBLANK can be bigger than the number of rows. Neither function is wrong: they simply do not use the same definition of “empty”.
Use the three together to audit a column
Side by side, the three functions become a diagnostic tool. Excel has no COUNTTEXT function, but you can build it by subtraction: COUNTA counts everything, COUNT counts only the numbers, so the difference is the number of text entries.
=COUNTA(C2:C16)-COUNT(C2:C16)
On the attendance list, the four results are 5, 12, 3 and 7:
- 5 numeric values (COUNT);
- 12 cells filled in (COUNTA);
- 3 people never checked in (COUNTBLANK);
- 7 answers typed as text (COUNTA − COUNT).
The column isn’t broken: it was just filled in two different ways, and now you know how many of each. The full exercise above then shows how to find the text cells with ISTEXT and fix them with IF.
As soon as you need a condition, for example count only the “Yes”, use COUNTIF, or COUNTIFS for several criteria.
