Home › Excel functions › COUNT & COUNTA
COUNT Function in Excel: COUNT, COUNTA and COUNTBLANK
Excel has three basic counting functions. COUNT counts numbers, COUNTA counts every non-empty cell, and COUNTBLANK counts empty cells. Picking the wrong one is a common source of wrong totals.
COUNT & COUNTA · In this guide
Syntax
| Function | Syntax | Counts |
|---|---|---|
| COUNT | =COUNT(value1, [value2], …) | Cells with numbers (dates and times count too) |
| COUNTA | =COUNTA(value1, [value2], …) | Cells that aren't empty: numbers, text, errors, TRUE/FALSE |
| COUNTBLANK | =COUNTBLANK(range) | Empty cells |
Example: the same range, three results
| A | |
|---|---|
| 1 | Hours |
| 2 | 8 |
| 3 | sick |
| 4 | 7.5 |
| 5 | |
| 6 | 6 |
| 7 | n/a |
| Formula | Result | Why |
|---|---|---|
=COUNT(A2:A7) | 3 | Only 8, 7.5 and 6 are numbers |
=COUNTA(A2:A7) | 5 | Everything except the empty A5 |
=COUNTBLANK(A2:A7) | 1 | Only A5 is empty |
Which one should you use?
- How many numeric entries? COUNT. For example, how many days have hours logged.
- How many rows are filled in? COUNTA on a column that's always filled, such as a name or ID column.
- How many are still missing? COUNTBLANK.
- How many meet a condition? COUNTIF or COUNTIFS.
Pitfalls
- Numbers stored as text are not counted by COUNT, only by COUNTA.
- Formulas that return "" (empty text, for example from
=IF(B2>0, B2, "")) look empty, but COUNTA counts them. COUNTBLANK also counts them as blank, so COUNTA + COUNTBLANK can exceed the number of cells. - A space typed in a cell makes it non-empty for COUNTA.
- Filtered lists: to count only visible rows, use
=SUBTOTAL(103, A2:A100). See SUBTOTAL.
Frequently asked questions
What's the difference between COUNT and COUNTA?
COUNT only counts cells with numbers. COUNTA counts every cell that isn't empty, including text.
Why does COUNTA count cells that look empty?
They contain something invisible: a space, or a formula that returns empty text (""). Check a cell with =LEN(A2).
How do I count cells with text only?
Use =COUNTIF(A2:A100, "*"). The asterisk matches any text, but not numbers.
How do I count unique values?
In Microsoft 365 or Excel 2021+, use =COUNTA(UNIQUE(A2:A100)).