Function guides · Statistical
COUNTBLANK function
Counts the empty cells in a range.
Syntax
=COUNTBLANK(range)
| Argument | What it is |
|---|---|
range | The range to check. It must be a range, not a list of values. |
What COUNTBLANK does
COUNTBLANK counts the empty cells in a range. It is the direct way to answer “how many are missing”.
It takes one argument, and that argument has to be a range. A cell that contains a formula returning an empty string counts as blank, which makes it more forgiving than a manual subtraction of COUNTA from the number of rows.
It does not count cells holding a space, a zero or any other value, even if they look empty on screen.
For conditional blank counts, COUNTIFS is the tool. COUNTIFS(A2:A100,"West",C2:C100,"") counts the West rows that are missing a value in column C. The empty string criterion matches both empty cells and cells whose formula returns an empty string.
Data-quality checks often run the three counting functions side by side: COUNTA for filled, COUNTBLANK for missing, ROWS for the total. The first two should add up to the third. If they do not, something in the range is neither blank nor filled in the way you expected, which usually means a stray space or a hidden formula.
COUNTBLANK examples
1. Missing phone numbers
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Phone | ||
| 2 | Hale Pools | 555-0114 | 2 | |
| 3 | Kona Print | |||
| 4 | Maui Tile | 555-0182 | ||
| 5 | Oahu Lumber | |||
| 6 | Pearl Cafe | 555-0107 |
Two of the five cells are empty, so COUNTBLANK returns 2. This is the quickest way to size a data-cleaning job.
2. A formula that returns an empty string is counted
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Note | ||
| 2 | Mei | VIP | 2 | |
| 3 | Omar | |||
| 4 | Kai |
The cell B3 holds a formula that returns "". It looks empty, and COUNTBLANK treats it as empty, though COUNTA would count it as filled.
3. Percent complete
| A | B | |
|---|---|---|
| 1 | Field | |
| 2 | Name | 0.5 |
| 3 | ||
| 4 | ||
| 5 | ||
| 6 | ||
| 7 | City | |
| 8 | ||
| 9 | Zip |
The share of a form that is filled in is one minus the blanks divided by the cells. Four of eight fields are empty here, so the form is 50 percent complete.
Common COUNTBLANK mistakes
- Passing a list instead of a range. COUNTBLANK needs a range.
=COUNTBLANK(A2,B2)is an error. - Cells with a single space. A space is not empty. COUNTBLANK skips it, so the count is too low. Clean with TRIM first.
- Counting zeros as blanks. A zero is a number and is not counted. Use COUNTIF(range,0) for zeros.
- Subtracting COUNTA from ROWS by hand. It works until a cell holds an empty string, where the two methods disagree. COUNTBLANK treats formula blanks as blank.
- Counting beyond the data. A range that runs past the last row counts every empty cell below the data too.
COUNTBLANK on Excel job tests
"How many customers have no email on file" or "how many survey cells are unanswered" calls for COUNTBLANK. A test can set it beside COUNT and COUNTA so that you have to pick by wording.
Make sure the range ends where the data ends. A range that runs past the last row adds hundreds of phantom blanks. If cells contain a single space they will not count as blank, so run TRIM first or the number comes out low.
Conditional versions use COUNTIFS with "" as one criterion, for example the blank phone numbers in the West region.
Related functions
COUNTBLANK questions
- What counts as blank for COUNTBLANK?
- Truly empty cells and cells holding a formula result of an empty string.
- Does COUNTBLANK count cells with spaces?
- No. A space is a character, so the cell is not blank.
- How do I count blanks with a condition?
- Use COUNTIFS with "" as one criterion, for example
=COUNTIFS(A:A,"West",B:B,""). - How do I count the cells that are filled in?
- Use COUNTA, which counts every non-empty cell.
Practice COUNTBLANK
Try it in a working grid: the free lesson Wildcards, dates and blanks in COUNTIF (module 8 of Everyday Excel) has checked COUNTBLANK exercises.
Practice this function