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
- JavaScript String substring() Explained
- Splunk Split Command: How to Use It
- Python split() and join() Explained with Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Why Won’t PowerPoint Add Slide Numbers? Common FixesPowerPoint usually will not show slide numbers because the number option was not applied to every slide, the…
- Excel Not Copying Formula When Dragging? How to FixIf Excel is not copying a formula when you drag, first check that the fill handle is enabled:…
- Excel Power Query Data Format Error: Causes and FixesAn Excel Power Query DataFormat.Error usually means a value does not match the data type or format Power…
- Excel Not Running Formula? Fix Calculation and Cell IssuesIf Excel is not running a formula, first check Formulas > Calculation Options > Automatic, then confirm the…
- Excel Formula Not Calculating? Fix Zero ResultsIf an Excel formula shows zero, first check Formulas > Calculation Options > Automatic, then confirm the formula…
- Excel Alt+H+O+I Not Working? Fix AutoFit Column WidthIf Excel’s Alt+H+O+I shortcut is not working, press the keys in sequence rather than holding them all down:…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.