COUNT vs COUNTA in Excel (and COUNTBLANK): Which to Use
COUNT counts numbers, COUNTA counts anything that isn't empty, COUNTBLANK counts empty cells. Examples, differences and the mistakes that give odd counts.
By Ayoub Farid · · 2 min read
Excel has several counting functions, and picking the wrong one is a classic reason for "why does it say 0?". Here's the difference in one table, then the details.
| Function | Counts | Example use |
|---|---|---|
| COUNT | Cells with numbers (including dates) | How many months have sales figures |
| COUNTA | Cells that aren't empty | How many names are on the list |
| COUNTBLANK | Empty cells | How many forms are missing a value |
| COUNTIF | Cells that match a condition | How many orders are "Paid" |
COUNT: count the numbers
=COUNT(B2:B13)Counts numbers only. Text, blanks and errors are skipped. Use it to check how many values a SUM or AVERAGE is really using.
COUNTA: count anything that isn't empty
=COUNTA(A2:A100)Counts any non-empty cell: text, numbers, dates, even errors. Use it to count names, entries or rows of data.
Watch out: a cell that looks empty but holds a space, or a formula returning "", counts as non-empty for COUNTA.
COUNTBLANK: count the empty cells
=COUNTBLANK(C2:C100)Counts empty cells. Formulas that return "" are counted as blank here, unlike COUNTA. That inconsistency surprises everyone once.
Counting with conditions
For "how many are Coffee" or "how many are above 5,000", use COUNTIF:
=COUNTIF(B2:B13,">5000")For several conditions at once, COUNTIFS.
Why does COUNT return 0?
Almost always because the numbers are stored as text, which happens with imported data. Signs: numbers are left-aligned, or there's a small green triangle in the cell corner. Fix: select the column, then Data > Text to Columns > Finish. Or use COUNTA if you just need to count entries.
Practical examples
- Number of employees:
=COUNTA(A2:A500) - Number of completed scores:
=COUNT(C2:C40) - Missing scores:
=COUNTBLANK(C2:C40) - Percentage complete:
=COUNT(C2:C40)/COUNTA(A2:A40)
Practise counting
COUNT shows up in Cellio's "Profit & COUNT" lesson, where you mix it with operators on a real sheet. Your answer is calculated and re-checked on hidden data, so you can't pass with a typed-in number.
Frequently asked questions
What is the difference between COUNT and COUNTA?
COUNT counts only numbers; COUNTA counts every cell that isn't empty, including text.
How do I count cells with text in Excel?
Use =COUNTIF(A2:A100,"*"), which counts cells containing any text. See count cells with text.
Does COUNT include dates?
Yes. Dates are stored as numbers, so COUNT includes them.
Why does COUNTA count blank-looking cells?
They contain something invisible, like a space or a formula returning "".