COUNTIF Not Including Zero? Excel Formula Examples

In Excel for Microsoft 365, use =COUNTIF(A2:A20,"<>0") to count cells whose values are not zero, assuming your data is in A2:A20. If the range contains blank cells that you do not want counted, use =COUNTIFS(A2:A20,"<>0",A2:A20,"<>") instead; this adds a separate nonblank test.

The basic COUNTIF formula

COUNTIF checks a range against one condition and returns how many cells meet it. Its structure is =COUNTIF(range,criteria). In the criteria, the operator <> means “not equal to,” so the condition <>0 means “not equal to zero.” Excel requires the comparison condition to be in quotation marks when it is typed directly into the formula.

For a concrete example, suppose A2:A7 contains 0, 5, -3, 12, 0, and 8. With those six values in A2:A7, enter =COUNTIF(A2:A7,"<>0"). The result is 4: the values 5, -3, 12, and 8 are counted, while the two zeros are not. Negative numbers count because they are not equal to zero.

The formula counts matching cells, not their values. It does not add the nonzero numbers together. To total nonzero values, use a sum formula with an appropriate condition, or first identify the matching records; COUNTIF is specifically for counting.

How the comparison criteria work

COUNTIF criteria can include comparison operators such as equals, greater than, less than, and not equal to. These are useful when “not zero” is only one part of the question. For instance, a count of positive values is different from a count of all nonzero values: negative entries qualify as nonzero, but they do not qualify as positive.

To count values greater than zero, use the criterion ">0".
To count values below zero, use "<0".
To count values other than a value stored in D1, join the operator to the cell reference, as in "<>"&D1.

For example, with values in A2:A20 and the comparison value in D1, enter =COUNTIF(A2:A20,"<>"&D1). If D1 contains 0, the formula counts entries other than zero. If you later change D1 to 5, it counts entries other than 5, without editing the formula. This is useful for a reusable worksheet where the excluded value may change.

Exclude blank cells as well as zero

A range may contain both zeroes and empty cells. These are not the same thing: zero is a value, while a blank cell has no entered value. If the question is “How many populated cells contain something other than zero?”, make both conditions explicit with COUNTIFS. This avoids relying on how a comparison criterion treats empty cells.

Assume A2:A8 contains 0, 5, -3, a genuinely empty cell, 12, 0, and 8. Enter =COUNTIFS(A2:A8,"<>0",A2:A8,"<>"). The result is 4. The first condition rejects zero, and the second rejects the empty cell. COUNTIFS applies both conditions to the same range, so a cell must pass both tests to be counted.

COUNTIFS is also convenient when the data must meet additional requirements. For example, you could count nonzero entries only for a specific department by adding a department range and a department criterion. Each added range must be the same size and shape as the other criteria ranges. If they do not align, the result may be wrong or Excel may return an error.

Numbers, text, and formula results

Spreadsheet values that look alike can be stored differently. A numeric zero is not identical in storage to the text character “0,” even though both may appear as 0 in a cell. Imported data, pasted values, and formulas that combine text and numbers can create this distinction. If a count looks unexpected, check whether the cells are numbers or text rather than assuming that their display tells the whole story.

The basic COUNTIF condition is designed for a general not-equal comparison; it is not a strict “count numeric nonzero values only” test. If a column might contain labels such as “pending” and you want to count only actual numbers other than zero, use SUMPRODUCT with ISNUMBER. With mixed data in A2:A10, enter =SUMPRODUCT(--ISNUMBER(A2:A10),--(A2:A10<>0)). For example, if the range contains 0, 5, “pending,” -2, and a blank among its entries, the numeric nonzero entries are 5 and -2, so the result is 2.

A formula that displays an empty string is another case worth checking. A cell containing a formula such as one that returns "" can look blank without being an actually empty cell. If these cells affect the result, test the workbook with a small sample and decide whether formula-generated empty strings should count as blank for your purpose. A numeric-only count using ISNUMBER avoids counting text labels, but the rule you want for empty strings should still be clear.

Common reasons the count seems wrong

When the returned count does not match a manual check, inspect the data and the formula in a consistent order. Most surprises come from the selected range, hidden distinctions in the cell contents, or a mismatch between the question and the criterion.

The range includes the wrong cells. Confirm the first and last row, and check whether a header was included. A header is text, not a zero, so a broad not-equal criterion may treat it as a match.
Some values are text. Imported numbers may be stored as text. Use Excel’s error indicators or inspect a few cells, and choose a numeric-only method if text should be ignored.
There are blanks or formulas returning empty text. Decide whether these should count, then add a nonblank condition or use a numeric-only test as appropriate.
The cells only look like zero. Custom number formatting can display a small number as 0 or display an actual zero as a dash. Check the underlying value, not just its appearance.
The wrong sign condition was used. A criterion greater than zero excludes negative numbers. Use not equal to zero when both positive and negative nonzero values should count.
The range is filtered. COUNTIF counts qualifying cells in its specified range whether rows are visible or hidden by a filter. Filtering the display does not make COUNTIF count only visible rows.

To inspect a suspicious cell, select it and look at the formula bar and number format. You can also temporarily widen the displayed decimals or change the format to General. These checks help distinguish an actual zero from a value that merely appears to be zero because of formatting.

Choosing between COUNTIF and other methods

Use COUNTIF when there is one condition, such as “not equal to zero.” Choose COUNTIFS when the count depends on multiple conditions or when you want to explicitly require a nonblank cell. Use SUMPRODUCT with ISNUMBER when the requirement is specifically to count numeric values and ignore text. The right choice depends on what should qualify, not on which formula is shortest.

For a positive-only count, assume the values are in A2:A20 and enter =COUNTIF(A2:A20,">0"). This counts positive entries and returns 0 for an empty range with no qualifying values. It does not include negatives. For an all-nonzero numeric count in a mixed range, use the SUMPRODUCT pattern described above rather than assuming text entries should be treated like numbers.

COUNTIF also works when the excluded value is in another cell. If A2:A20 holds the data and D1 holds the value to exclude, =COUNTIF(A2:A20,"<>"&D1) makes the criterion adjustable. For a multi-condition question, such as “nonzero amount and status is Paid,” COUNTIFS is usually easier to read because each range-condition pair is visible in the formula.

Entering and maintaining the formula in Excel

In Excel for Microsoft 365 on Windows, select the result cell, type the formula beginning with an equals sign, and press Enter. The same formulas work in Excel for the web. Excel for Mac uses the same function and criteria logic; menu placement and keyboard shortcuts can differ, but there is no different COUNTIF formula for Mac.

If the data grows regularly, consider converting the range to an Excel table using Insert > Table. A table expands as rows are added and can make formulas easier to maintain. You can write a structured-reference COUNTIF formula using the table’s column name, but the ordinary cell-range version is often simpler for a fixed dataset. Check that any formula points to the intended column after inserting or moving worksheet columns.

COUNTIF is not case-sensitive for text comparisons, but that detail usually does not affect a numeric zero test. Also, a criterion entered as text must be quoted within the formula, while a reference to a criterion cell is joined with the operator using the ampersand. Keeping those two forms straight prevents common syntax errors.

Frequently Asked Questions

Does COUNTIF with not equal to zero count blank cells?
Blank-cell behavior can make a plain not-equal criterion surprising. If blanks must be excluded, use COUNTIFS with both a not-zero test and a not-blank test, such as =COUNTIFS(A2:A20,"<>0",A2:A20,"<>").

How do I count nonzero values but ignore text?
Use SUMPRODUCT with ISNUMBER and a not-equal-to-zero test. This counts numeric nonzero entries while excluding text labels. It is a better fit than a plain COUNTIF when the range may contain mixed content.

Does COUNTIF count negative numbers as not zero?
Yes. Negative numbers are different from zero, so they meet the not-equal-to-zero condition. Use a greater-than-zero criterion instead if only positive numbers should count.

Can I count zeroes instead of excluding them?
Yes. With data in A2:A20, use =COUNTIF(A2:A20,0) to count cells equal to zero. If the source data may contain text versions of zero, check how those entries are stored and whether they should be included.

Will COUNTIF count only the visible rows after I filter a list?
No. COUNTIF evaluates the full range you give it, including rows hidden by filtering. If the count must respond to visible rows only, you need a visibility-aware approach rather than a standard COUNTIF formula.

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.