Excel COUNTIF: Count Cells That Aren’t Errors

Formulas & functions · Updated November 19, 2026

COUNTIF does not have a reliable criterion for “is not an error.” To count cells that contain no error, with data in A2:A100, use =SUMPRODUCT(--NOT(ISERROR(A2:A100))); this counts blank cells too, because a blank is not an error.

Why COUNTIF cannot test for errors

COUNTIF is designed to count cells that meet a value-based condition, such as matching text, equaling a number, or being greater than a number. Its criteria can use comparison operators and wildcards, but an Excel error is a distinct cell value—not ordinary text that can be reliably matched with a criterion such as “not #VALUE!” or “not #N/A.”

For example, COUNTIF can count cells containing the word Open. With status values in A2:A100, use =COUNTIF(A2:A100,"Open"). That formula checks for a particular value; it does not check whether each cell evaluates to an error. A criterion like <> means “not equal to” a specified value, not “is not an error.”

A common near miss is counting nonempty cells. With records in A2:A100, =COUNTIF(A2:A100,"<>") uses the not-blank criterion. It does not express an error test, so it is not the right tool when the requirement is specifically to distinguish errors from non-errors. Use an error-checking function for that job.

Count every cell that is not an error

ISERROR returns TRUE when its input is an error and FALSE otherwise. NOT reverses those results, and the double unary (two minus signs) converts TRUE and FALSE to 1 and 0. SUMPRODUCT adds the resulting values across the range, which gives a count.

With the cells to inspect in A2:A100, use =SUMPRODUCT(--NOT(ISERROR(A2:A100))). Suppose A2:A11 contains ten cells, two of which show errors and eight of which contain ordinary values or are blank. The formula returns 8. It deliberately includes empty cells: a genuinely blank cell does not contain an error.

This approach is useful when the question is “How many cells in this fixed range are safe to use as non-error results?” It also works when the errors are produced by formulas, such as a division by zero or a lookup that cannot find a match. The formula checks the evaluated result in each cell, rather than the formula text.

Decide whether blanks should count

“Not an error” and “contains a usable value” are different conditions. A blank passes the first condition but may not pass the second. Decide which total you need before choosing a formula, especially when blank cells represent missing records.

  • Count non-errors, including blanks: With the range A2:A100, use =SUMPRODUCT(--NOT(ISERROR(A2:A100))). A blank contributes one to the count.
  • Count non-error, nonempty cells: With records in A2:A100, use =COUNTA(A2:A100)-SUMPRODUCT(--ISERROR(A2:A100)). COUNTA counts nonempty cells, including error results, and the second part subtracts the error cells.

The second formula has a detail worth knowing: COUNTA counts a cell containing a formula even if that formula displays an empty string, such as a formula that returns "". Such a cell is not physically empty, so it remains in the total unless it is an error. If your definition of blank includes formula results that look empty, that distinction matters; check the actual data and the intended meaning of “nonempty.”

Count errors instead, or check for one error type

Sometimes the useful number is the error count, rather than the valid-cell count. With the cells to inspect in A2:A100, use =SUMPRODUCT(--ISERROR(A2:A100)). ISERROR recognizes error results; for a ten-cell range with two errors, the formula returns 2. You can subtract that result from the total number of cells when you want to calculate a non-error count that includes blanks.

If you want to count one particular error, compare the cell’s error type rather than trying to turn the error into a text criterion. For example, ERROR.TYPE returns a number for an error and #N/A when its input is not an error, so a direct array comparison can be awkward. A simpler worksheet design is often to test each cell with IFERROR and return a clear label in a helper column, then count that label with COUNTIF. This makes the classification visible and easier to audit.

For a direct count of all errors except #N/A, ISERR is the relevant test. With data in A2:A100, use =SUMPRODUCT(--ISERR(A2:A100)). ISERR detects Excel errors other than #N/A; ISERROR also detects #N/A. Choose based on whether a missing lookup result is an error you want included. Newer Excel versions also have additional error types, and ISERROR is generally the simpler all-error check.

Use a helper column for row-level decisions

A single SUMPRODUCT result gives a total, but it does not tell you which row contains an error. If you need to filter, review, or explain individual records, a helper column can label each result. With the value to inspect in A2, enter =IF(ISERROR(A2),"Error","OK") in B2 and fill it down. The output identifies each row, and you can count the OK labels separately.

This is particularly helpful in a shared workbook. A reviewer can see that a row is marked Error and investigate the original calculation, instead of relying on a summary number alone. It also avoids hiding the source problem: replacing an error with zero or blank may make a report look cleaner, but can conceal missing or invalid data.

If the source range changes frequently, format the data as an Excel table with Insert > Table. A table column can expand as rows are added, and structured references can make formulas easier to read. The core principle stays the same: use ISERROR to detect errors; use COUNTIF only for criteria that COUNTIF is designed to evaluate.

Common mistakes and troubleshooting

  • Using <> as if it meant “not an error.” The operator means “not equal to” a value or criterion. It does not identify an error state.
  • Forgetting that blanks count. The SUMPRODUCT and ISERROR formula counts blanks as non-errors. If that is not intended, use a nonempty-cell approach and verify how formula-generated empty strings should be treated.
  • Counting only one kind of error. ISERR excludes #N/A, while ISERROR includes it. A lookup failure may therefore change the result depending on which function you choose.
  • Using text that looks like an error. The text “#N/A” typed into a cell is not the same as an actual #N/A error value. ISERROR returns FALSE for ordinary text, even when that text resembles an error message.
  • Expecting the total to identify the source. A count tells you how many cells meet the condition, not which cells they are. Use a helper column or filter the source data to locate the problem rows.

If a formula returns an unexpected total, check the exact range, confirm whether apparently blank cells contain formulas, and inspect whether the cells show actual Excel errors or text that merely resembles them. These checks resolve many apparent COUNTIF problems before the formula itself needs changing.

Compatibility and where to enter the formula

SUMPRODUCT, NOT, ISERROR, ISERR, COUNTA, and COUNTIF are established Excel functions and are available in Microsoft 365 for Windows. The formulas above do not require a newer dynamic-array function. Enter the selected formula in a result cell and press Enter; no special array-entry shortcut is needed.

The same formulas generally work in Excel for Mac and Excel for the web. Ribbon placement and some interface details differ, but these formulas do not depend on a Windows-only command. If Excel uses semicolons rather than commas as the argument separator under your regional settings, use the separator Excel expects; the examples here use the standard comma syntax.

Frequently Asked Questions

Can COUNTIF count cells that are not errors?
Not reliably as an error-status test. COUNTIF evaluates criteria such as matching text or numbers. Use SUMPRODUCT with NOT and ISERROR to count cells whose results are not errors.

Does the non-error formula count blank cells?
Yes. With =SUMPRODUCT(--NOT(ISERROR(A2:A100))), a blank is counted because it is not an error. Use COUNTA minus the error count if you want to exclude physically empty cells.

What is the difference between ISERROR and ISERR?
ISERROR returns TRUE for all Excel error values, including #N/A. ISERR detects errors other than #N/A. Use ISERROR when you want a broad check for any error.

Can I count cells that are not #N/A?
Yes, but decide whether other error types should count as acceptable. A broad ISERROR test rejects every error type; ISERR deliberately treats #N/A differently. For mixed rules, use a helper column with explicit tests so the logic is easy to review.

Why does a cell showing #N/A not behave like the text “#N/A”?
An actual #N/A is an Excel error value, while the characters #N/A entered as text are just text. ISERROR detects the actual error, not the text string. This distinction applies to other error-looking text as well.

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.