Math
How many cells are empty
Definition
Counting empty cells in a range means giving COUNTBLANK that range.
Syntax
=COUNTBLANK(range)
Arguments
| Argument | Meaning |
|---|---|
range |
The block of cells to scan for gaps, such as B2:B5. |
Worked example
An attendance list records replies in B2:B5. Two names have no reply yet.
| A: Name | B: Reply |
|---|---|
| Ann | yes |
| Bo | |
| Cal | yes |
| Dee |
Formula: =COUNTBLANK(B2:B5)
Result: 2. B3 and B5 are empty, so two cells are gaps.
Practice
Cell C2 holds 1. Cells C3:C5 are empty. In D2, write a formula that counts how many cells in C2:C5 are empty.
- In D2, write a COUNTBLANK formula for C2:C5.
- D2 should show how many empty cells are in C2:C5.
Step 1 of 2
Enter =COUNTBLANK(C2:C5) in D2.
C3, C4, and C5 are empty, so the count is 3.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
D2 uses COUNTBLANK.
D2 now shows 3.
The next lesson is Removing extra spaces from text.
Related functions
COUNTBLANK is the function that counts the empty cells in that range.
Common mistake
Scanning the name column, as in =COUNTBLANK(A2:A5): every name is filled, so the result is 0 instead of 2.