COUNT, COUNTA, and COUNTBLANK all count cells, and each one counts a different set. COUNT(value1, [value2], ...) counts only the cells that hold numbers. COUNTA(value1, [value2], ...) counts every cell that is not empty, whatever it holds. COUNTBLANK(range) counts the empty cells. The catch that trips people is a cell whose formula returns an empty string: COUNTA reads it as filled, COUNTBLANK reads it as blank, so the same cell can land in both totals.
None of the three takes a condition. Counting only the cells that meet a rule is what COUNTIF and COUNTIFS do, and that is a separate job, covered in the COUNTIF and SUMIF guide.
What does each function count?
The three split the cells in a range into three overlapping groups.
- COUNT counts numbers, and nothing else. It walks the range and tallies the cells holding a number, so
COUNT(value1, [value2], ...)on a column of figures returns how many are numeric. Dates count, because Excel keeps a date as a number underneath. Text, blank cells, logical values, and error cells sitting inside the range are passed over. - COUNTA counts any cell that is not empty. It tallies every filled cell regardless of content: text, numbers, dates, the logical values TRUE and FALSE, error values, and a formula whose result is an empty string all count toward
COUNTA(value1, [value2], ...). The only cell it leaves out is one that is genuinely empty. It even counts a cell showing an error such as#DIV/0!, since the cell is not empty; the error catalog covers what the codes mean. - COUNTBLANK counts the empty cells.
COUNTBLANK(range)takes a single range and returns how many cells in it are blank.
Which cells does each one count?
Put a number, a piece of text, an empty cell, and a formula that returns an empty string in one column, and the three functions divide them up like this:
| A | B | |
|---|---|---|
| 1 | Name | Reply |
| 2 | Ada | 42 |
| 3 | Bo | Pending |
| 4 | Cy | |
| 5 | Di |
Run all three over B2:B5:
| Formula | Returns | Which cells it counts |
|---|---|---|
=COUNT(B2:B5) |
1 | only B2, the number 42 |
=COUNTA(B2:B5) |
3 | B2, B3, and B5 (the empty-string formula counts as filled) |
=COUNTBLANK(B2:B5) |
2 | B4 and B5 (the empty-string formula counts as blank) |
COUNT finds one number, so it returns 1. COUNTA finds three filled cells, the number, the text, and the formula in B5, so it returns 3. COUNTBLANK finds two blanks, the empty B4 and that same formula in B5, so it returns 2. Notice B5 sits in both the COUNTA total and the COUNTBLANK total: across four cells, 3 plus 2 comes to 5, because that one formula cell is counted on both sides.
Is a formula that returns nothing blank?
This is the distinction the two functions were built to disagree on. A cell whose formula produces an empty string shows nothing, but it is not empty, because a formula lives in it. Microsoft’s COUNTBLANK page is explicit: “Cells with formulas that return “” (empty text) are also counted. Cells with zero values are not counted.“ COUNTA takes the opposite view and counts that same cell as an entry.
This matters when you build a report on top of such a column. If you count filled responses with COUNTA, the empty-string cells inflate the total; if you count the gaps with COUNTBLANK, they are treated as gaps. Decide which reading you want before you wire the count into anything downstream.
Why does COUNT come back lower than you expect?
Inside a range, COUNT counts real numbers and nothing else. That is usually what you want, but it becomes a trap when a column that looks fully numeric holds a few figures stored as text. Those text entries are not numbers to COUNT, so they drop out of the tally with no error, and the count reads lower than the rows on screen. It is the same reason a SUM can come out low, and there is a full guide to numbers stored as text. A quick tell: COUNT below COUNTA on a column that should be all numbers means some of those numbers are text.
One wrinkle applies here, though it rarely bites in practice. Values typed straight into the arguments follow a looser rule than values pulled from a range: a logical value or a number written as text does count when you type it directly, so =COUNT(TRUE, "1", 5) returns 3. Point COUNT at cells holding those same values and only the genuine number is counted. Because you almost always give COUNT a range, the range rule is the one that governs everyday use.
If the rows you are counting are filtered, a plain COUNT still counts the ones hidden by the filter. The filter-aware version is SUBTOTAL, whose function numbers 2 and 3 give you a COUNT and a COUNTA that follow the active filter, covered in the SUBTOTAL guide.
The short version
- COUNT counts numbers only, and dates count because a date is a number underneath. COUNTA counts any non-empty cell. COUNTBLANK counts the empty cells.
- Their signatures are
COUNT(value1, [value2], ...),COUNTA(value1, [value2], ...), andCOUNTBLANK(range). COUNT and COUNTA take up to 255 arguments; COUNTBLANK takes one range. - A formula that returns an empty string is non-blank to COUNTA and blank to COUNTBLANK, so one such cell is counted by both, and the two totals can add up to more than the cells in the range.
- COUNT coming back below COUNTA on a column that should be all numbers points at numbers stored as text, which COUNT skips over entirely.
- To count with a condition, reach for COUNTIF or COUNTIFS in the COUNTIF and SUMIF guide; to count only the rows a filter leaves visible, SUBTOTAL is the filter-aware version.
