Conditional formula page
Count Blank Cells Formula
Use COUNTBLANK when blanks are the data quality problem you want to measure.
Best for
Count blank cells in a spreadsheet range.
What it returns
The formula counts empty cells in A2:A100.
Copy formulas
=COUNTBLANK(A2:A100)=COUNTBLANK(A2:A100)Excel and Google Sheets use the same COUNTBLANK(range) syntax. The function counts genuinely empty cells and formulas that return an empty string.
Example data
| Region | Status | |
|---|---|---|
| maya@example.com | East | Active |
| noah@example.com | West | Active |
| maya@example.com | East | Active |
| West | Inactive |
What it returns
The formula counts empty cells in A2:A100.
How the formula works
- COUNTBLANK checks one range.
- It returns the number of empty cells.
- Use this before removing blank rows or validating required fields.
| Syntax piece | Role in the formula |
|---|---|
| A2:A100 | The range whose blank-looking cells should be counted. |
| blank criterion | COUNTBLANK applies its blank test implicitly; no second criterion is supplied. |
| result | The returned number of cells treated as blank. |
Verified examples
=COUNTBLANK(A2:A100)Excel: Use the sample duplicate table where the Email column has one blank. Returns: 1
=COUNTBLANK(B2:B5)Google Sheets: Use the sample sales table and inspect B2:B5. Returns: 0
=COUNTBLANK(A2:A3)Excel: Set A2 to ="" and leave A3 genuinely blank. Returns: 2
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| A cell containing a space is not counted | A space is text, not an empty cell. | Remove spaces or test trimmed values separately with a formula such as LEN(TRIM(A2))=0. |
| The count is unexpectedly large | The selected range includes unused rows or columns beyond the intended data. | Narrow the range to the actual input area. |
| Formula blanks are counted | COUNTBLANK treats formulas returning "" as blank. | Use a different condition when formula-generated empty strings must be distinguished from truly empty cells. |
When not to use this formula
- Do not use COUNTBLANK when cells containing only spaces should count as blank without first cleaning the text.
Alternatives
| Alternative | When to use it |
|---|---|
| COUNTIF Nonblank Formula | Use when the report needs to count cells that contain values instead. |
| Remove Blank Rows Formula | Use when blank rows should be excluded from a returned table. |
Related formulas
Official references
- COUNTBLANK function from Microsoft
FAQ
Does an empty-string formula count as blank?
Yes. COUNTBLANK counts a cell whose formula evaluates to "".
Does a single space count as blank?
No. A space is text, so clean it first if it should be treated as empty.