The COUNTBLANK, COUNTA guide
=COUNTBLANK(C2:C13) counts the cells that look empty, formulas that return "" included. COUNTA counts every cell that isn't empty, and a cell with a formula in it isn't, even when it shows nothing.
Who counts what
| Cell holds | COUNTA | COUNTBLANK |
|---|---|---|
| Nothing | no | yes |
| A formula showing "" | yes | yes |
| Text or a number | yes | no |
| 0 | yes | no |
A formula that returns "" is counted by both, so COUNTA plus COUNTBLANK can come to more than the number of cells. ISBLANK is TRUE only for the first row: a cell with a formula in it is never blank to ISBLANK.
Where "" comes from
Lookups are the usual source: =IFERROR(VLOOKUP(A2, $E$2:$F$11, 2, FALSE), "") shows nothing when there's no match. The column looks half empty, but every cell has a formula in it. "Rows minus COUNTA" then says nothing is missing.
Count the filled ones
To count the cells that show something in a column of formulas, take the blanks away: =COUNTA(C2:C13)-COUNTBLANK(C2:C13), or =ROWS(C2:C13)-COUNTBLANK(C2:C13). For text values, =COUNTIF(C2:C13,"?*") counts cells with at least one character.
Spaces
In Excel, a cell with a single space looks empty but isn't: COUNTA counts it and COUNTBLANK doesn't. TRIM or Find and Replace clears them out.
The plain version of these counts is in COUNT vs COUNTA.
FAQ
- How do I count blank cells in Excel?
- Use =COUNTBLANK(range). It counts empty cells and cells whose formula returns "".
- Why does COUNTA count cells that look empty?
- They hold something: a formula that returns "", or a space. COUNTA counts any cell that isn't truly empty.
- How do I count cells that are not blank?
- =COUNTA(range) when the cells hold typed values. When they hold formulas that may return "", use =COUNTA(range)-COUNTBLANK(range).
- Does COUNTBLANK count zeros?
- No. A 0 is a value, so COUNTBLANK leaves it out.



































































