How to Count Blank Cells in Google Sheets With COUNTBLANK
=COUNTBLANK(value1, [value2, ...]). Cells with no content and cells holding an empty string "" both count as blank.
What COUNTBLANK does
COUNTBLANK answers "how many cells are missing?" It is the mirror image of COUNTA, which counts the cells that have something in them. Use it to spot incomplete forms, missing phone numbers or unfilled rows.
COUNTBLANK syntax
=COUNTBLANK(value1, [value2, ...])
Each argument is a value or range to check. Google's help page states that COUNTBLANK treats cells with no content, and cells containing an empty string "", as blank. That second case matters: a formula that returns "" looks empty and is counted as blank.
A step-by-step example
Here is a contact list in cells A1:B6. Some names and phone numbers are missing.
| A | B | |
|---|---|---|
| 1 | Name | Phone |
| 2 | Ann | 111 |
| 3 | 222 | |
| 4 | Ben | |
| 5 | 444 | |
| 6 | Cara |
=COUNTBLANK(A2:A6)
This returns 2: the names in A3 and A5 are missing. To check both columns at once, pass the whole block:
=COUNTBLANK(A2:B6)
This returns 4: two missing names and two missing phone numbers.
COUNTBLANK vs COUNTA
For a range of 5 cells, COUNTA plus COUNTBLANK should add up to 5 when every blank is truly empty. If they do not, some cells probably hold values that look empty, such as a space. A space is not an empty string, so COUNTBLANK does not count it.
Common mistakes
- Cells that look empty but hold a space. They are not blank to COUNTBLANK.
- Including the header row in the range.
- Expecting a count of rows. It counts cells, so a two-column range counts each column.
Try it yourself
Use the table above (A1:B6). Type your formula into the box, starting with =, then press Check.
Exercise 1: Missing names
Write a formula that counts the blank cells in the Name column, A2:A6.
Exercise 2: Whole block
Write a formula that counts the blank cells in the whole block A2:B6.
What to learn next
- COUNT and COUNTA: count numbers or non-empty cells.
- Count cells with text: count text cells and partial matches.
- COUNTIF: count the rows that match a condition.
Syntax and rules in this guide were checked against Google's COUNTBLANK help page. See all guides.