COUNTBLANK function
COUNTBLANK counts the blank cells in a range, which is handy for spotting missing data. Cells holding an empty string from a formula are counted as blank too.
Example
Read it as a sentence: count the empty cells in D1:D5, which gives 5.
Excel shows 5
D
1
2
3
4
5
Data
Syntax
COUNTBLANK(range)
-
range(required) - Range in which to count empty cells.
Returns: The number of empty cells in the range.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=COUNTBLANK(Data!D1:D5) | 5 | All blank |
=COUNTBLANK(Data!B1:B8) | 1 | Mixed range (one blank) |
=COUNTBLANK(Data!C1:C5) | 0 | Range with error |
=COUNTBLANK(Data!D1) | 1 | Single blank cell |
=COUNTBLANK(Data!AP1) | 1 | Empty string from formula counts as blank |
The Data sheet these formulas read
A cell that starts with = holds that formula.
B
C
D
AP
1
10
1
=""
2
20
2
3
TRUE
=NA()
4
4
5
abc
5
6
30
7
FALSE
8
40
Data
Watch out for
- Needs a range; an array constant gives #VALUE!.
- Cells with zero are not blank.
Where it works
- Excel
- COUNTBLANK is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .