COUNTIF 0 in Excel: Count Zero Values and Blank Cells

Formulas & functions · Updated November 22, 2026

To count cells that match zero in Excel, use =COUNTIF(A2:A20,0), replacing A2:A20 with your range. Be aware that blank cells can affect a zero count; if you need to count numeric zeros only, use a formula that checks the cell’s data type as well.

What COUNTIF 0 means

COUNTIF counts cells in a range that meet one condition. In this case, the condition is a value of zero. The function follows the pattern COUNTIF(range,criteria): the first argument is the cells to examine, and the second is the condition to match. The formula returns a count, not a list of the matching cells.

For a simple inventory or survey, this is often all you need. If column B contains quantities and zero means an item is out of stock, a zero count tells you how many entries currently have no stock. COUNTIF can also count zeros in scores, hours, balances, or other numeric data.

Excel stores numbers separately from how it displays them. A cell formatted to show no decimal places might display a small value such as 0.3 as 0, but the stored value is not actually zero. COUNTIF with a zero criterion checks the cell value, not merely the rounded appearance on screen. That difference matters when a count seems inconsistent with what you see.

How to enter the formula

For example, with values in A2:A20, enter this formula in an empty cell outside that range:

With values in A2:A20, the formula counts cells matching zero:

=COUNTIF(A2:A20,0)

In Microsoft 365 for Windows, select the cell where you want the result, type the formula in the cell or formula bar, and press Enter. Excel normally displays the result immediately. If you are using a different column, replace the range with the appropriate cells, such as C2:C100. A range can be a single column, a single row, or a rectangular block.

The criterion can also be written as text in quotation marks: =COUNTIF(A2:A20,"=0"). The equals sign makes the exact-match condition explicit, but it is optional for a straightforward zero count. Use commas between arguments in standard English-language Excel installations; some regional settings use semicolons as argument separators.

Keep the result cell outside the cells being counted. If you include the formula cell in its own range, Excel may report a circular reference or calculate an unintended result, depending on the setup. For a reusable formula copied down a report, lock the range with dollar signs: with values in A2:A20, =COUNTIF($A$2:$A$20,0) keeps the counted range fixed when copied.

Work through a small example

Suppose A2:A8 contains, in order, 0, 12, a genuinely empty cell, 0, -4, 8, and 0. There are three cells containing numeric zero. However, COUNTIF’s zero criterion can also match empty cells in the range, so the basic formula returns 4 for this particular layout. The arithmetic is three zero entries plus one blank cell.

With those values in A2:A8, this formula returns 4 because the range includes one blank:

=COUNTIF(A2:A8,0)

This behavior is a frequent source of confusion. If every cell in the range is populated, the basic formula gives the count of zero entries as expected. If the range has empty cells, its result may be higher than the number of visible numeric zeros. A quick way to check is to temporarily select the suspected blank cells and inspect the formula bar, or use a separate blank count.

If you want to count only numeric zeros, with values and a blank in A2:A8 as described above, use a type check as well:

=SUMPRODUCT(--ISNUMBER(A2:A8),--(A2:A8=0))

This returns 3. ISNUMBER is TRUE for numeric entries and FALSE for blanks or text; the second test checks for equality to zero. SUMPRODUCT combines the tests and counts only cells where both are true. This is useful when the source data may mix numbers, empty cells, and text such as a typed-in “0”.

Count blanks separately

When you need to understand why a COUNTIF total seems too large, count the blanks as a separate check. With the example range A2:A8 containing one genuinely empty cell, this returns 1:

=COUNTBLANK(A2:A8)

COUNTBLANK counts empty cells and also cells whose formulas return an empty text string, such as a formula that displays nothing. That distinction can matter in a report: a cell that looks blank may contain a formula, while another may be truly empty. COUNTBLANK treats both as blank for its count.

You can also use COUNTIF to count blank-looking cells. With the same range, this formula returns 1 for the single genuinely empty cell:

=COUNTIF(A2:A8," ")

That formula contains a space between the quotation marks, so it searches for a space character rather than a blank. To count empty cells with COUNTIF, use empty quotation marks instead:

With A2:A8 containing one genuinely empty cell, this returns 1:

=COUNTIF(A2:A8,"")

Do not confuse an empty criterion with a criterion containing a space. A space is text and is not the same as an empty cell. If cells may contain spaces copied from another source, consider cleaning the data before relying on blank counts.

Use a cell as the criterion

A criterion can come from a worksheet cell instead of being typed directly into the formula. This is handy when a user needs to change the target value without editing the formula. Suppose A2:A8 contains 0, 12, blank, 0, -4, 8, and 0, while D2 contains numeric 0. With that layout, this formula returns 4 because the criterion is zero and the range includes a blank:

=COUNTIF(A2:A8,D2)

Changing D2 to 8 makes the formula count cells matching 8 instead. A cell reference is especially useful in dashboards or templates where a drop-down or input cell controls the result. Make sure the criterion cell contains the intended type of value; a number stored as text can behave differently from a numeric entry in some comparisons.

For criteria that combine an operator with a value in another cell, join the operator to the cell reference with an ampersand. For example, to count numbers greater than the value in D2, with data in A2:A20 and a numeric threshold in D2, use =COUNTIF(A2:A20,">"&D2). This is not needed for an exact zero match, but it is useful when adapting the same function to thresholds.

Common problems and checks

  • The result is higher than expected: Check whether the range includes blank cells. The basic zero criterion can count blanks, so use the numeric-only SUMPRODUCT formula if blanks should not qualify.
  • A displayed zero is not counted as expected: The cell may contain a small nonzero value formatted to look like zero. Increase the displayed decimal places or inspect the formula bar and underlying value.
  • Some zeros are text: Imported data may contain the character 0 rather than the number zero. If the distinction matters, use ISNUMBER to count numeric zeros, or convert the source data to numbers before analysis.
  • The formula shows an error or is treated as text: Confirm that the entry begins with an equals sign and that the cell is not formatted as Text. If it is, change the cell format to General, then re-enter the formula.
  • The range misses recently added rows: Update the range or use an Excel Table so formulas can refer to a growing data column. Avoid selecting an unnecessarily large range if workbook performance or clarity is a concern.

Another useful check is to filter the source column for zero and inspect the visible records. Filtering helps you see which rows are being treated as matches, although a filter view is not a replacement for checking blanks, text values, or underlying precision. If the data comes from a CSV or an external system, inconsistent types are particularly common.

COUNTIF compared with other options

COUNTIF is the simplest choice when you need one criterion in one range. It is more readable than a longer array formula for a routine count, and it recalculates as worksheet values change. For numeric zero only, however, the SUMPRODUCT version is more explicit about excluding text and blank entries.

COUNTIFS is designed for two or more conditions, including conditions applied to different ranges. It can be useful when the task is not simply “count zeros” but “count zeros for a particular category.” For instance, a report might need to count zero quantities only for rows labeled “North.” Ensure that the criteria ranges have matching dimensions so each row’s conditions are evaluated together.

Filtering is a practical alternative when the goal is to inspect or edit matching rows rather than calculate a total. Select the data, use Data > Filter in Excel for Windows, and open the filter menu for the relevant column to select zero. A PivotTable can also summarize counts by value when you need a broader breakdown. These tools answer related questions, but COUNTIF is generally faster to set up for a single total in a worksheet cell.

Frequently Asked Questions

Does COUNTIF with zero count blank cells?

It can. A zero criterion may include blank cells in the selected range, which is why a basic zero count can exceed the visible number of zero entries. Use a numeric-only test when blanks must be excluded.

How do I count only numeric zeros?

With the data in A2:A20, use =SUMPRODUCT(--ISNUMBER(A2:A20),--(A2:A20=0)). It counts cells that are numbers and equal to zero, excluding blanks and text entries.

How do I count cells that are not zero?

With values in A2:A20, =COUNTIF(A2:A20,"<>0") counts cells that do not match zero. If blanks must also be excluded, check the range contents carefully or add a separate nonblank condition.

Can I count zeroes in more than one range?

Yes. With separate values in A2:A20 and C2:C20, add two COUNTIF results: =COUNTIF(A2:A20,0)+COUNTIF(C2:C20,0). Remember that blanks in either range may affect the total.

Why does a cell that looks like zero not count?

It may only display as zero because of number formatting or rounding. Increase the decimal places or inspect the stored value. COUNTIF evaluates the underlying value, not just the way the cell is displayed.

Choosing the right zero count

Use COUNTIF(range,0) for a quick count when the range is clean and blanks are not a concern. If the worksheet contains empty cells and you mean actual numeric zeros, use the SUMPRODUCT and ISNUMBER test. If your goal is to inspect matching records, filter the column; if you need counts across several conditions, consider COUNTIFS. Choosing the formula based on what “zero” means in your data prevents a correct calculation from answering the wrong question.

Related

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.