Math
COUNTBLANK
Definition
COUNTBLANK returns how many cells in a range are empty.
Syntax
=COUNTBLANK(range)
Arguments
| Argument | Meaning |
|---|---|
range |
The cells to check, such as B2:B6. |
Worked example
A rota has some shifts still unfilled. The shift names are in B2:B6.
| A: Slot | B: Name |
|---|---|
| 1 | 1 |
| 2 | |
| 3 | |
| 4 | 4 |
| 5 |
Formula: =COUNTBLANK(B2:B6)
Result: 3. B3, B4, and B6 are empty, so three cells count as blank.
Practice
Cell A2 holds 1, cell A3 is empty, cell A4 holds 2, and cell A5 is empty. In B2, write a formula that counts how many cells in A2:A5 are empty.
- In B2, write a COUNTBLANK formula for A2:A5.
- B2 should show how many empty cells are in A2:A5.
Step 1 of 2
Enter =COUNTBLANK(A2:A5) in B2.
A3 and A5 are empty, so the count is 2.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
B2 uses COUNTBLANK.
B2 now shows 2.
Related functions
COUNTA counts filled cells in the same range. COUNT counts only numeric cells. COUNTBLANK is the count of the gaps.
Common mistake
Treating a cell that holds 0 as blank: 0 is a value, so COUNTBLANK does not include it.