Excel COUNTIF Not NA: Exclude #N/A Errors

Errors & fixes · Updated November 22, 2026

To count cells in Excel while excluding #N/A errors, use =COUNTIF(range,"<>#N/A") for exact matches, or better, =SUMPRODUCT(--(ISNA(range)=FALSE)) to count all non-error cells. The first formula counts cells that do not literally contain the text #N/A, while the second counts cells that are not #N/A errors, which is usually what you want.

Why #N/A Errors Break COUNTIF

COUNTIF counts cells that meet a condition, but it treats error values like #N/A, #VALUE!, and #DIV/0! as special. If you use a normal criteria like "<>0", COUNTIF will ignore error cells entirely, not count them as non-matching. So if your range has #N/A errors, COUNTIF returns a count that excludes those cells, but it does not tell you how many errors exist. Worse, if you try to count non-error cells with COUNTIF(range,"<>#N/A"), Excel interprets the criteria as text, not as an error type, so it will actually count cells that contain the literal text “#N/A” (which is rare) and will still ignore real #N/A errors.

How to Count Cells Excluding #N/A Errors

There are two main approaches, depending on what you need:

  • Count all non-error cells: Use =SUMPRODUCT(--(ISNA(range)=FALSE)). This counts every cell that is not a #N/A error, including other error types. With data in A2:A10, it returns the number of cells that are not #N/A.
  • Count cells that meet a condition and ignore #N/A: Use =SUMPRODUCT((range<>"")*(ISNA(range)=FALSE)*(range="Yes")) for text, or =SUMPRODUCT((ISNA(range)=FALSE)*(range>10)) for numbers. These array-like formulas work without Ctrl+Shift+Enter in modern Excel.

For example, with values in A2:A10 containing {1, #N/A, 3, #N/A, 5}, the formula =SUMPRODUCT(--(ISNA(A2:A10)=FALSE)) returns 3, because three cells are not #N/A. If you want to count only numbers greater than 2, use =SUMPRODUCT((ISNA(A2:A10)=FALSE)*(A2:A10>2)), which returns 2 (3 and 5).

Using COUNTIF with Wildcards to Exclude Errors

Some users try =COUNTIF(range,"<>#N/A*") to exclude #N/A, but this still does not work because COUNTIF cannot match error values with wildcards. The only way to make COUNTIF work is to first remove or convert errors. For example, you can wrap the range in IFERROR: =COUNTIF(IFERROR(A2:A10,""),"Yes") but this requires array entry (Ctrl+Shift+Enter in older Excel, or just Enter in Excel 365). In Excel 365, you can also use =COUNT(FILTER(A2:A10,ISNA(A2:A10)=FALSE)) to count numeric non-errors.

Comparing COUNTIF, SUMPRODUCT, and FILTER

Here is a quick comparison:

  • COUNTIF: Simple, but cannot handle errors directly. Use only when you are sure there are no errors, or after cleaning data.
  • SUMPRODUCT: Works in all Excel versions, no array entry needed, and can combine multiple conditions. Slightly more complex syntax.
  • FILTER (Excel 365/2021): Modern and readable, but only available in newer versions. Example: =ROWS(FILTER(A2:A10,ISNA(A2:A10)=FALSE)) counts non-#N/A cells.

For most users, SUMPRODUCT is the safest cross-version choice.

Common Problems and Solutions

Problem 1: COUNTIF returns 0 even though you have non-error cells. This happens if your criteria is a text match and the cells contain errors. Solution: Use SUMPRODUCT with ISNA.

Problem 2: You want to count only numbers, ignoring text and errors. Use =SUMPRODUCT(--ISNUMBER(A2:A10)) – this counts numeric cells, and because errors are not numbers, they are excluded.

Problem 3: Your range includes #N/A from VLOOKUP, and you want to count how many matches were found. Instead of counting non-errors, count the matches directly: =COUNTIF(B2:B10,"Found") if that is your output.

Alternatives: Cleaning Data First

Often the best practice is to prevent errors from appearing in the first place. Use IFERROR in your lookup formulas: =IFERROR(VLOOKUP(D2,A:B,2,FALSE),""). Then you can use regular COUNTIF on the results. This also makes your spreadsheet more readable. If you already have errors, you can replace them with Find & Replace (Ctrl+H), but be careful: replacing #N/A with a blank will change the cell value to text, not a true blank. Instead, use a helper column: =IF(ISNA(A2),"",A2) and copy down.

Frequently Asked Questions

Q: Can I use COUNTIF to count cells that are not #N/A?
A: Not directly. COUNTIF cannot match error types. Use =SUMPRODUCT(--(ISNA(range)=FALSE)) to count non-#N/A cells.

Q: Why does COUNTIF ignore #N/A errors?
A: COUNTIF treats errors as unmatched, so they are simply not counted. This is by design; errors are not values that can satisfy a comparison.

Q: How do I count cells that are not errors of any type?
A: Use =SUMPRODUCT(--ISERROR(range)=FALSE) to count cells that are not any error. Or =ROWS(range)-SUMPRODUCT(--ISERROR(range)).

Q: Does SUMPRODUCT work in Excel for Mac?
A: Yes, SUMPRODUCT works in Excel for Mac exactly as in Windows. The syntax is the same.

Q: What if I have #N/A in a pivot table source?
A: Pivot tables ignore errors by default, but they can cause issues. It is better to clean the source data with IFERROR before creating the pivot.

Q: Can I use COUNTIFS with multiple criteria to exclude #N/A?
A: COUNTIFS also cannot exclude errors. Use SUMPRODUCT with multiple conditions, e.g., =SUMPRODUCT((ISNA(A2:A10)=FALSE)*(B2:B10="Yes")).

Related

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.