Count Cells That Are Blank? Excel & Sheets
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
This article explains how to count blank cells in Excel and Google Sheets. You will learn the COUNTBLANK function, how to handle cells with formulas that return empty text, and how to count truly empty cells versus visually empty ones. The answer is straightforward: use =COUNTBLANK(range) to count cells that are completely empty, and use =COUNTIF(range,””) to count cells that appear empty, including those with formulas returning “” .
Formula
The COUNTBLANK function counts cells in a range that are truly empty—no value, no formula, no spaces. The syntax is:
=COUNTBLANK(range)
Where range is the group of cells you want to check. For example, =COUNTBLANK(A1:A10) returns the number of empty cells in that range.
In Google Sheets, you can also use =COUNTIF(range, “”) to count cells that appear empty, including those with formulas that return an empty string. In Excel, COUNTIF with “” counts cells that are truly empty or contain formulas returning “” (depending on the version).
Worked Example
Suppose you have data in cells A1:A10 with the following values:
- A1: 5
- A2: (blank)
- A3: hello
- A4: (blank)
- A5: =”” (formula returning empty text)
- A6: 0
- A7: (blank)
- A8: (space character)
- A9: 10
- A10: (blank)
Using =COUNTBLANK(A1:A10), the function counts cells that are completely empty. In this list, A2, A4, A7, and A10 are truly blank—that’s 4 cells. A5 contains a formula, so it is not counted. A8 contains a space, which is not blank. So COUNTBLANK returns 4.
Using =COUNTIF(A1:A10, “”), the result depends on the application. In Google Sheets, COUNTIF with “” counts cells that are empty or contain formulas returning “”, so it would count A2, A4, A5, A7, and A10—that’s 5. In Excel, COUNTIF with “” typically counts only truly empty cells in older versions, but in modern Excel it also counts formulas returning “”. So the result may be 4 or 5 depending on your version.
If you want to count cells that are visually empty (including spaces and formulas returning “”), you can use =COUNTIF(range, “”) + COUNTIF(range, ” “) but that gets messy. A better approach is to use =SUMPRODUCT(–(TRIM(range)=””)) which treats spaces as empty.
When to Use Each Method
Choose the method based on what you mean by “blank”:
- Truly empty cells (no data, no formula, no spaces): Use COUNTBLANK.
- Visually empty cells (including formulas returning “” or cells with spaces): Use COUNTIF(range, “”) in Google Sheets, or SUMPRODUCT with TRIM for a robust solution.
- Cells that are not blank (the opposite): Use =COUNTA(range) which counts non-empty cells, but note it counts cells with formulas even if they return “”.
For example, if you have a column of survey responses and some cells contain formulas that return “” when no response is given, COUNTBLANK will undercount the blanks. In that case, use COUNTIF(range, “”) to get the true number of unanswered questions.
Common Mistakes and Misconceptions
One common mistake is assuming a cell with a space is blank. A space is a character, so COUNTBLANK will not count it. This often happens when data is imported from other sources.
Another issue is that COUNTBLANK counts cells with formulas that return an empty string (“”) in some versions of Excel? Actually, no—COUNTBLANK does not count cells with formulas, even if they return “”. This is a frequent source of confusion. For example, =COUNTBLANK(A1:A10) will ignore A5 in the example above, even though it displays as empty.
Also, be aware that COUNTBLANK counts cells that contain apostrophes (‘) or zero-length strings from other functions. For instance, a cell with =”” is not counted, but a cell with a formula that returns a zero-length string (like =IF(1=2,””,”x”) which returns “x”) is not blank anyway.
How to Count Blank Cells in Excel and Google Sheets
In both Excel and Google Sheets, the steps are similar:
- Select the cell where you want the result.
- Type =COUNTBLANK( and then select the range with your mouse or type it manually.
- Close the parenthesis and press Enter.
For Google Sheets, you can also use =COUNTIF(range, “”) to include formulas returning empty text. In Excel, you might need to use =SUMPRODUCT(–(TRIM(range)=””)) to count cells that are visually blank, including spaces.
Alternatives and Related Functions
If you need more control, consider these functions:
- COUNTA – counts non-empty cells. Use it to find how many cells have data.
- COUNT – counts cells with numbers only.
- COUNTIF – counts cells that meet a condition, such as =COUNTIF(range, “”) for blanks.
- SUMPRODUCT – can combine conditions, e.g., =SUMPRODUCT(–(TRIM(range)=””)) to count visually blank cells ignoring spaces.
- FILTER (in Google Sheets) – can list blank cells, but not directly count them.
Frequently Asked Questions
Why does COUNTBLANK not count cells with formulas that return “”?
COUNTBLANK only counts cells that are truly empty—no formula, no value, no spaces. A cell with a formula is considered non-empty, even if the formula returns an empty string. To count those, use COUNTIF(range, “”) in Google Sheets or SUMPRODUCT with TRIM in Excel.
How do I count blank cells in a filtered list?
COUNTBLANK and COUNTIF ignore filters; they count all cells in the range, including hidden rows. To count blanks only in visible rows, you need a more complex formula like =SUMPRODUCT(SUBTOTAL(3,OFFSET(range,ROW(range)-MIN(ROW(range)),0,1)),–(range=””)) which works in both Excel and Google Sheets.
What is the difference between COUNTBLANK and COUNTIF with “”?
COUNTBLANK counts only truly empty cells. COUNTIF with “” counts cells that are empty or contain formulas returning an empty string, but it does not count cells with spaces. In Google Sheets, COUNTIF(range, “”) counts both truly empty cells and formulas returning “”. In Excel, behavior varies by version.
How do I count cells that are not blank?
Use =COUNTA(range). It counts all cells that contain any value, including numbers, text, errors, and formulas that return a value. Note that COUNTA counts cells with formulas that return “” as non-blank, which might not be what you want.
Can I count blank cells in a specific column but only for rows where another column has a value?
Yes, use COUNTIFS. For example, to count blank cells in column B where column A is not empty, use =COUNTIFS(A:A,”<>“,B:B,””). This counts rows where A is not blank and B is blank.
Why does my COUNTBLANK return 0 when I see empty cells?
This usually happens because the cells contain spaces, apostrophes, or formulas returning “”. Try =SUMPRODUCT(–(TRIM(range)=””)) to count cells that are visually empty. Also check for non-breaking spaces (CHAR(160)) which are not trimmed by TRIM.
Related
Formula used
in r | Range | - | A1:A10 | 0 out count | Blank cells | count | countblank(r) | 0
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.