To count blank cells in Excel, use =COUNTIF(A2:A20,""), replacing A2:A20 with your range. COUNTIF can also count cells that look blank because a formula returns an empty string, so the right approach depends on what you mean by “blank.”
How COUNTIF counts blanks
COUNTIF checks a range against a criterion and returns the number of cells that meet it. For a blank criterion, use two quotation marks with nothing between them. Excel interprets that as an empty text value; it can match cells that are empty as well as cells whose formulas display no text.
For example, suppose A2 is genuinely empty, A3 contains North, A4 contains a formula returning an empty string, A5 is empty, A6 contains 0, and A7 contains South. With those entries in A2:A7, the following formula returns 3: A2, A4, and A5 meet the blank criterion.
With those example entries in A2:A7, use =COUNTIF(A2:A7,""). The result is 3. If your actual range has no formula-generated empty strings, this is usually the simplest way to count blanks.
COUNTIF is useful because it can be combined with other criteria and automatically updates as cell values change. It does not, however, decide whether a cell is meaningfully missing for your particular task. A formula that returns an empty string, a cell containing a space, and a truly unused cell can look similar on the worksheet while behaving differently in some calculations.
COUNTIF versus COUNTBLANK
Excel also has a dedicated function called COUNTBLANK. It takes a range and returns the number of cells Excel treats as blank. Like COUNTIF with an empty-string criterion, COUNTBLANK includes genuinely empty cells and cells containing formulas that return an empty string. It is often easier to read when the only goal is to count blanks.
Using the example range A2:A7 described above, the formula is =COUNTBLANK(A2:A7), and its result is 3. COUNTBLANK has no criteria argument, so it cannot directly add a condition such as “blank in column A and Open in column B.” Use COUNTIF for a single condition, or COUNTIFS when multiple conditions must all be met.
Neither function should be read as a count of cells that have never been used. If a formula is present but displays nothing, the cell is still occupied by a formula even though these blank-counting functions treat its displayed result as blank. If your question is whether a cell is physically empty, use a test designed for that distinction instead.
Count only truly empty cells
ISBLANK tests whether a cell is empty, rather than whether its displayed result appears empty. It returns TRUE for a cell with no value or formula, but FALSE for a cell containing a formula such as one that returns an empty string. It also returns FALSE for a cell containing a space.
With the example entries in A2:A7, use =SUMPRODUCT(--ISBLANK(A2:A7)) to count physically empty cells. The result is 2, because only A2 and A5 are genuinely empty. SUMPRODUCT combines the TRUE/FALSE tests across the range and counts the TRUE results. This distinction matters when auditing whether source data was entered, rather than simply identifying rows that appear blank.
For one individual cell, you can check whether it is truly empty with =ISBLANK(A2). With A2 genuinely empty, the result is TRUE. If A2 contains a formula returning an empty string, the result is FALSE. Be aware that functions such as SUMPRODUCT can be less intuitive to readers than COUNTIF, so document the reason for using this stricter test when sharing a workbook.
Count blanks that meet another condition
To count rows where one column is blank and another has a particular value, use COUNTIFS. It applies each range-and-criterion pair together, so a row is counted only when all of its conditions are satisfied. The criteria ranges should have the same shape and cover the same rows.
Suppose A2:A10 contains owners and B2:B10 contains statuses. To count rows with a blank owner and the status Open, use =COUNTIFS(A2:A10,"",B2:B10,"Open"). The result is the number of rows in which both conditions hold; for example, if exactly two Open rows have no owner, the formula returns 2. A formula-generated empty string in the owner column can also qualify as blank.
You can use a cell reference as the blank criterion when that is more convenient than typing an empty-string criterion into the formula. If D2 is empty, then =COUNTIF(A2:A20,D2) uses D2’s empty value as the criterion. This can help make a criterion adjustable from the worksheet, but make sure D2 itself does not contain a formula or an unnoticed space if you expect it to be empty.
What counts as blank: spaces, zero, and formulas
A cell containing one or more spaces is not empty. It may look blank because the characters are hard to see, but a blank criterion will not reliably treat that space as an empty cell. The same is true of other invisible characters, such as a nonbreaking space copied from a web page. These are common causes of unexpectedly low blank counts.
- Truly empty cell: Has no value or formula; COUNTIF with an empty criterion counts it.
- Formula returning an empty string: Displays no text; COUNTIF with an empty criterion and COUNTBLANK count it, while ISBLANK returns FALSE.
- Space character: Contains text, even if it is difficult to see; it is not a truly empty cell.
- Zero: Is a numeric value, not a blank; a blank criterion does not count it.
If you want to count cells containing exactly one ordinary space, specify a criterion with one space between the quotation marks. With the values to inspect in A2:A20, use =COUNTIF(A2:A20," "). This does not catch every possible invisible character, and it counts only cells whose contents match that space criterion.
For cleanup, inspect suspicious cells in the formula bar or test them with ISBLANK. You can also use Find and Replace to remove known unwanted characters, but review the data and make a backup before replacing values across a large range. TRIM can remove ordinary leading, trailing, and repeated spaces from text, but it does not remove every kind of nonprinting or nonbreaking character. Cleaning imported data may require identifying the specific character first.
Counting nonblank cells
To count cells that are not blank, reverse the criterion with the not-equal operator. With values in A2:A20, use =COUNTIF(A2:A20,"<>"). This counts entries that do not meet the blank criterion. It is a practical companion to the blank count when you want to check how many cells in a fixed range contain something.
For example, if the range contains 19 cells and COUNTIF with an empty criterion returns 4, the nonblank count should be 15 when both formulas use the same range and the same interpretation of blank. A difference from the expected total can point to a missed row, a mistyped range, or content that only looks empty. Formula results and unusual characters are especially worth checking when totals do not reconcile.
Do not confuse a blank with zero. A cell containing 0 is populated, even if the worksheet’s number format hides the zero or a setting makes zero values less visible. If a count looks wrong, check the underlying value in the formula bar and review number formatting before changing the formula.
Common problems and troubleshooting
When a blank count is unexpected, start by confirming the range. A formula that stops at row 20 will not include new entries in row 21. If the list is likely to grow, consider converting it to an Excel table using Insert > Table, then use the table’s column references so the range can expand with the data.
- Check for formulas: A cell displaying nothing may contain a formula. COUNTIF with an empty criterion can include formulas that return an empty string, but ISBLANK will not.
- Look for spaces: Select a suspicious cell and inspect its contents. A space is text, not an empty cell.
- Check the criterion: The blank criterion is two quotation marks with no space between them. Adding a space changes the criterion.
- Confirm the selected rows: Ensure the range covers the intended data and does not include headers or unrelated cells.
- Check calculation settings: If formula results have not updated, confirm that workbook calculation is set to Automatic under Formulas > Calculation Options.
COUNTIF is not case-sensitive for ordinary text criteria, but that detail usually does not affect blank counting. Also remember that a cell can be visually empty because its font color matches the background, its contents are hidden by formatting, or a custom number format suppresses a value. In those situations, COUNTIF evaluates the underlying cell content, not just what is visible on the screen.
Choosing the right formula
Choose the formula based on the question you actually need to answer. For an ordinary count of blank-looking cells, COUNTIF with an empty criterion is concise. COUNTBLANK is clearer when you only need a blank count. For a count of cells that have no value or formula whatsoever, use an ISBLANK test. For blanks that also meet another condition, choose COUNTIFS.
- Simple blank count: Use COUNTIF(range, empty criterion) or COUNTBLANK(range).
- Physically unused cells only: Use SUMPRODUCT with ISBLANK.
- Blank plus another condition: Use COUNTIFS with a matching pair of criteria ranges.
- Spaces or imported characters: Clean or inspect the contents instead of assuming the cells are empty.
These formulas are available in Excel for Microsoft 365 on Windows. On Mac and Excel for the web, the same COUNTIF, COUNTIFS, COUNTBLANK, ISBLANK, and SUMPRODUCT formulas work; the main differences are in the interface and how you access the workbook, not in these formula examples. If your regional Excel settings use semicolons as argument separators, Excel may display or require semicolons instead of commas.
Frequently Asked Questions
What is the formula to count blank cells in Excel?
Use =COUNTIF(A2:A20,""), replacing A2:A20 with your range. It counts empty cells and can also count cells with formulas returning an empty string.
Does COUNTIF count cells with a formula that returns nothing?
Yes, the empty-string criterion generally treats a formula result of an empty string as blank. If you need to distinguish those cells from cells with no formula or value, test with ISBLANK instead.
Is COUNTBLANK better than COUNTIF for blank cells?
For a simple blank count, COUNTBLANK is often easier to read. COUNTIF is useful when you want to combine the blank criterion with other criteria or use a criterion reference.
How do I count cells that are not blank?
Use =COUNTIF(A2:A20,"<>") for the cells that do not match a blank criterion. Check for formulas returning empty strings or unusual cell contents if the result differs from your expected total.
Why does COUNTIF not count a cell that looks empty?
The cell may contain a space, a nonprinting character, or a value hidden by formatting. Inspect the cell contents; visually blank does not always mean empty.
Related
- Excel Data Model: What It Is and How to Use It
- Excel Data Model: Can You Turn It Off?
- Excel Data Model and SharePoint: How They Work
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Sum a Range of Cells: Formulas, Tips, and FixesTo add a range of cells in Excel, use the SUM function: if the numbers are in A1…
- Excel ETA LAMBDA: Build a Reusable Arrival-Time FormulaExcel has no built-in function named ETA, but you can calculate an estimated arrival time with a normal…
- Excel: Test If a Cell Contains Specific TextIn Excel for Microsoft 365, test whether a cell contains specific text with =ISNUMBER(SEARCH("apple",A2)). It returns TRUE when…
- Excel Filter by Color and Value: How It WorksYes. In Excel for Microsoft 365 on Windows, open a column’s filter menu and choose a color under…
- Number Sequence in Excel: Fill Series or Use a FormulaTo create a number sequence in Excel, enter the first two numbers, select both cells, and drag the…
- Excel MID Function: Can It Return Variable-Length Text?Yes. Excel’s MID function can return a variable number of characters when you calculate its num_chars argument from…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.