COUNTIF in Excel: Count Empty Cells and Blank Cells Easily

If your cells are in A2:A20, use =COUNTIF(A2:A20,"") to count empty cells. COUNTIF with an empty-string criterion counts genuinely blank cells and cells whose formulas return an empty string, so use a different method if you need to distinguish those cases.

The basic COUNTIF formula for empty cells

COUNTIF takes two arguments: the range to check and the criterion that a cell must meet. To count blanks, put an empty pair of quotation marks as the criterion. The quotation marks tell Excel to look for an empty text value; they do not mean that you should leave the criterion argument out.

For example, with order references in A2:A20, the formula shown above returns the number of cells Excel treats as blank in that range. It does not count cells containing a number, ordinary text, or a space character. The range can be a row, a column section, or a larger rectangular block, provided the cells you want counted are included.

Enter the formula in a cell outside the range being counted, such as B2, and press Enter. If you put the result inside the range itself, the formula can include its own cell in the count, creating a circular reference or an unintended result. You can copy the formula and change the range when you need to count blanks in another part of the worksheet.

What Excel treats as an empty cell

It helps to separate a truly empty cell from a cell that only looks empty. A cell can appear blank while still containing a formula, a space, or other content. COUNTIF’s empty-string criterion is useful for most worksheet summaries, but it does not identify only cells that have never had content entered into them.

Truly empty cell: Nothing is stored in the cell. COUNTIF with the empty-string criterion includes it.
Formula returning an empty string: The formula is present, but its displayed result is blank. COUNTIF with the empty-string criterion also includes it.
Space character: The cell contains a space, so it is not empty. The space may be hard to see, but COUNTIF does not treat it as blank.
Zero: A numeric zero is a value, not a blank. It is not counted by the empty-string criterion, even if formatting makes the zero hard to notice.

This distinction matters in reports built from formulas. For instance, a formula may return an empty string until a related field is completed. The result looks empty and is counted as blank by this COUNTIF approach, even though the cell contains a formula. That behavior is often desirable for a visible report, but it may not be right for auditing whether users physically left cells untouched.

Worked example with blanks and formula results

Suppose A2:A10 contains the following: A2 has North, A3 is empty, A4 contains 0, A5 contains a formula that returns an empty string, A6 is empty, A7 contains one space, A8 has South, A9 is empty, and A10 contains 12. With this layout, the count is four: A3, A5, A6, and A9.

With those entries in A2:A10, =COUNTIF(A2:A10,"") returns 4. The formula counts the three genuinely empty cells and the formula result in A5. It does not count the zero in A4 or the space in A7. Those are common reasons a reported blank count may be higher or lower than a person’s visual estimate.

If you change A7 from a space to a truly empty cell, the answer becomes five. If A5’s formula is changed so that it returns a visible word, the answer falls to three. These checks can help you confirm whether a surprising result comes from the range, hidden content, or formula-generated blanks.

COUNTIF compared with COUNTBLANK

COUNTBLANK is a dedicated alternative for counting empty-looking cells. With the same data in A2:A10, =COUNTBLANK(A2:A10) returns 4 as well. It also counts cells containing formulas that return an empty string. For a straightforward blank count, it is often easier to read because its name describes the task.

COUNTIF is useful when you are already using conditional counts or want a criterion-based formula that can be adapted for other values. COUNTBLANK is simpler when the only question is how many blank cells are in a range. Neither function treats a cell containing a space as empty, and neither one distinguishes a truly empty cell from a formula result that displays nothing.

With entries in A2:A10 as described above, =COUNTA(A2:A10) returns 6. COUNTA counts cells containing values and also counts a cell that contains a formula returning an empty string. It also counts the space in A7. Therefore, it is not always safe to calculate blanks by subtracting COUNTA from the number of cells unless you have confirmed how formulas and invisible characters should be handled.

Count only cells that are truly empty

If your question is specifically whether a cell is physically empty, use ISBLANK rather than relying on the displayed result. ISBLANK returns TRUE for a genuinely empty cell, but FALSE for a cell containing a formula—even when that formula displays nothing. That makes it a better fit for audits where formulas should not be mistaken for unused cells.

With the example data in A2:A10, =SUMPRODUCT(--ISBLANK(A2:A10)) returns 3, counting A3, A6, and A9 only. SUMPRODUCT totals the TRUE results after the double unary operator converts them to 1 and the FALSE results to 0. This formula is longer than COUNTIF, so use it when the distinction is important rather than as a default replacement for every blank count.

To check one cell rather than count a range, select an unused cell and test a reference directly. If the cell contains a formula returning an empty string, ISBLANK reports FALSE because a formula is present. This is a useful diagnostic when two cells look identical but produce different blank counts.

Counting blanks by row, column, or condition

To count blanks down a column, use a vertical range such as A2:A100. For blanks across a row, use a horizontal range such as B4:K4. To count every blank in a rectangular area, use a range such as B2:F20. Check that the selected range does not include headings or totals unless those cells are meant to be part of the result.

COUNTIF supports one criterion at a time. If you need blanks that meet an additional condition—for example, blank delivery dates only for orders marked Open—COUNTIFS can apply more than one condition. The criteria ranges need to cover corresponding rows and have matching dimensions; otherwise, the result may not represent the records you intend to analyze.

For a list that grows over time, consider converting the data to an Excel table using Insert > Table. Tables expand as records are added, and formulas that refer to table columns can be easier to maintain than fixed ranges. Before using a structured reference in a shared workbook, confirm that the table column contains only the records you want counted; a total row or extra blank records can affect interpretation.

Common problems and quick checks

A COUNTIF blank count that seems wrong is usually caused by a range or content issue, not a complicated calculation. Before changing the formula, check the cells that look empty and verify what they actually contain.

The answer is too high: Look for formulas returning empty strings. They are included by the empty-string criterion. Also check whether the range accidentally includes extra rows or columns.
The answer is too low: Look for spaces or other characters in cells that appear empty. Select a suspect cell and inspect the formula bar; pressing Delete can remove unwanted content, but first confirm the cell should be cleared.
A zero is counted unexpectedly: Check whether the formula criterion was entered correctly and whether the cell is truly numeric zero. A zero is not a blank, although custom number formatting can hide its display.
The formula shows an error or is rejected: Confirm that the opening and closing parentheses match and that quotation marks around the blank criterion are straight double quotes. Some regional Excel settings use semicolons instead of commas as argument separators; follow the separator used by your installation.
The count changes after editing: Recalculate the workbook if calculation is set to Manual. On Windows, press F9 to calculate formulas; on Mac, the equivalent recalculation shortcut is usually Fn+F9 or F9 depending on the keyboard settings.

Also check for merged cells and hidden rows if you are comparing a formula result with what is visible on screen. Hiding a row does not remove its cells from the range, so COUNTIF can count blank cells in hidden rows. If you want a count based only on visible records, a plain COUNTIF over the full range is not a visibility-aware solution; filter the data and choose an approach designed to account for visible rows.

Using COUNTIF in Excel for Microsoft 365

The formula works in Excel for Microsoft 365 on Windows and uses the same function on Mac and Excel for the web. The main differences readers may encounter are interface details and regional settings, rather than how COUNTIF interprets the empty-string criterion. If your version uses semicolons between function arguments, Excel may adjust separators when you enter or edit a formula.

For a single, fixed dataset, a range such as A2:A20 is clear and predictable. For a worksheet that receives regular additions, a table reference or a deliberately sized range can reduce the chance that new records are left out. Avoid using a whole-column range automatically if the worksheet is large and the formula will be copied many times; a focused range is often easier to review and maintain.

COUNTIF does not need special settings, add-ins, or a particular cell format to count blanks. Formatting changes how a value looks, not whether a cell contains a value. For example, a custom format may make a zero appear invisible, but the cell still contains zero and will not become blank for this calculation.

Frequently Asked Questions

What is the COUNTIF formula for blank cells?

With the cells you want to check in A2:A20, use =COUNTIF(A2:A20,""). Replace A2:A20 with your own range. The formula counts genuinely empty cells and cells whose formulas return an empty string.

Does COUNTIF count a formula that returns nothing?

Yes. If a formula displays an empty string, COUNTIF with the empty-string criterion counts that cell as blank. If you need to count only cells with no content at all, use ISBLANK-based logic instead.

How do I count cells that are not blank?

The best method depends on what you mean by nonblank. COUNTA counts cells containing formulas even if they display an empty string, and it also counts spaces. If formula-generated blanks or invisible characters are possible, define the rule first and check sample cells rather than assuming every function classifies them the same way.

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. A formula result can also behave differently from a truly empty cell in tests such as ISBLANK. Inspect the formula bar and, if needed, test the cell with ISBLANK to distinguish an empty cell from one that contains a formula.

Can COUNTIF count blank cells in a whole column?

Yes. A whole-column range such as A:A can be used when you intend to include that entire column. It also includes the heading area and every other row in the column, so use a narrower range or a table column when only the data records should be counted.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.