Excel COUNT vs COUNTA: Which One Counts What
COUNT counts numbers; COUNTA counts anything that is not empty.
Excel has four counting functions and choosing the wrong one is the most common source of a total that is quietly out by a few. COUNT counts cells containing numbers. COUNTA counts cells that are not empty, whatever they hold. COUNTBLANK counts the empty ones. COUNTIF counts the ones meeting a condition.
The pair worth understanding properly is COUNT and COUNTA. Point both at a column of readings that includes a text label and a blank, and they disagree — which is exactly the point. COUNT tells you how many usable numbers you have; COUNTA tells you how many rows are filled at all. Comparing the two is a one-line audit of how clean an import is.
COUNTA has one trap that catches everybody. A cell holding a formula that returns "" looks empty and counts as filled, because it contains a formula result. That is why a COUNTA over a column of IF formulas can report far more rows than you can see.
Syntax
=COUNT(value1, …) =COUNTA(value1, …) =COUNTBLANK(range)Arguments
value1, …Required- Ranges or values to count. COUNT considers only numbers and dates; COUNTA considers anything non-empty.
rangeRequired- COUNTBLANK only, and unlike the others it takes exactly one contiguous range.
The example data
Headers in row 1, data in A2:D6. C4 is blank and C6 holds text.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Sensor | Reading | Calibration | Batch |
| 2 | North inlet | 17 | 3 | 12 |
| 3 | South inlet | -4 | 5 | 7 |
| 4 | Header tank | 63 | 12 | |
| 5 | Overflow | 8 | 2 | 9 |
| 6 | Return line | -21 | n/a | 7 |
Worked examples
=COUNT(C2:C6)Result: 3
Only the three numeric calibrations. The blank in C4 and the text "n/a" in C6 are not numbers.
=COUNTA(C2:C6)Result: 4
The text counts as filled, so COUNTA finds one more than COUNT.
=COUNTBLANK(C2:C6)Result: 1
The genuinely empty cell. COUNTA plus COUNTBLANK equals the row count.
=COUNTA(C2:C6) - COUNT(C2:C6)Result: 1
The audit: how many filled cells are not numbers. Anything above zero means the column needs cleaning.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
COUNTA is higher than the visible rows
Why it happens: Formulas returning "" occupy their cells. They look blank and count as filled.
How to fix it: Count meaningfully-filled cells instead: =COUNTIF(range, "<>").
COUNT returns 0 on a column of numbers
Why it happens: They are stored as text, which COUNT does not count.
How to fix it: Convert the column. COUNTA returning the right figure while COUNT returns 0 is the signature of this problem.
#VALUE! from COUNTBLANK
Why it happens: It was given several ranges. Unlike COUNT and COUNTA it accepts only one.
How to fix it: Add separate COUNTBLANKs together, or use COUNTIF with "" as the criterion.
Tips worth knowing
- COUNTA minus COUNT is the fastest way to find how many rows in a numeric column are not numeric.
- COUNTA plus COUNTBLANK always equals the number of cells in the range — a useful sanity check.
- COUNT counts dates, because Excel stores them as numbers.
- To count rows in a filtered list, use SUBTOTAL(103, range) rather than COUNTA.
Frequently asked questions
What is the difference between COUNT and COUNTA?
COUNT only counts cells containing numbers (and dates, which are numbers underneath). COUNTA counts every cell that is not empty, including text, errors and logical values. On a clean numeric column they agree; where they disagree, the difference is how many non-numeric entries you have.
Why does COUNTA count cells that look empty?
Because a cell holding a formula that returns "" is not empty — it contains a formula result that happens to be an empty string. Use =COUNTIF(range, "<>") to count only cells with something meaningful in them.
How do I count cells that are not blank and meet a condition?
COUNTIFS handles both at once: =COUNTIFS(A2:A100, "North", B2:B100, "<>"). The second pair requires the cell to be non-empty as well as the region matching.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.