Count Blank Cells in a Range

Excel & Google Sheets

How it works

COUNTBLANK counts every cell in A2:A20 that is empty, including cells whose formula returns "" — anything that displays as blank. It's the complement of COUNTA, which counts non-empty cells, so COUNTBLANK plus COUNTA always equals the size of the range. That pairing gives you an instant completeness check: =COUNTA(A2:A20)/ROWS(A2:A20) is the percentage filled in. Cells containing a space or a zero are not counted as blank, because they hold a real value even if a space is invisible.

COUNTBLANK

Counts cells that are empty or contain a formula returning empty text.

A2:A20

The range to check. Keep it to your actual data rows — don't use the whole column.

When to use it

Use this at the top of any tracker as a live missing-data counter: unfilled emails before a mail merge, tasks without owners, invoices without dates. When the count hits zero, the dataset is ready.

Common mistakes

• ✕Pointing it at a whole column and getting a huge number.

  ✓=COUNTBLANK(A:A) counts every empty row down to the bottom of the sheet — over a million in Excel. Limit the range to your data: =COUNTBLANK(A2:A20).

• ✕Cells with a single space aren't counted as blank.

  ✓A cell containing " " holds a value, so COUNTBLANK skips it even though it looks empty. Count those too with =SUMPRODUCT(--(TRIM(A2:A20)="")).

• ✕Expecting COUNTBLANK to ignore formulas that return "".

  ✓It won't — a cell showing empty text counts as blank. If you need to count only truly empty cells (no formula at all), use =SUMPRODUCT(--ISBLANK(A2:A20)).