Excel AVERAGEIF Not NA: Exclude Errors & Text

Formulas & functions · Updated November 22, 2026

To average a range in Excel while ignoring #N/A errors (and other errors), you cannot use AVERAGEIF alone—it ignores text and blank cells but not errors. The direct solution is to combine IFERROR with AVERAGE in an array formula, or use AGGREGATE with option 1 (or 6) to ignore errors. For example, with values in A2:A10, enter =AVERAGE(IFERROR(A2:A10,"")) and press Ctrl+Shift+Enter (or use =AGGREGATE(1,6,A2:A10) for a non-array alternative). This article explains why AVERAGEIF fails, how to apply these solutions, and how to handle related scenarios.

Why AVERAGEIF Doesn’t Ignore #N/A

AVERAGEIF is designed to average cells that meet a condition, such as being greater than zero or matching a text string. Its criteria argument cannot directly exclude errors like #N/A. When you use a criterion like “<>#N/A”, Excel treats it as text comparison, not an error check, so cells containing #N/A still cause the function to return #N/A. Similarly, AVERAGEIFS has the same limitation. Errors in the average range are not filtered by any criteria—they propagate through the calculation, making the result an error. This is a common misconception because AVERAGEIF does ignore text and empty cells, but it does not ignore error values.

Solution 1: AVERAGE with IFERROR (Array Formula)

The most straightforward method is to wrap each cell in IFERROR to convert errors to an empty string, then average the result. In Excel 365, this works as a dynamic array formula automatically; in older versions, you must enter it as an array formula with Ctrl+Shift+Enter.

  • Assumption: Values in A2:A10, some containing #N/A.
  • Formula: =AVERAGE(IFERROR(A2:A10,""))
  • How it works: IFERROR replaces each #N/A with an empty string (“”), which AVERAGE ignores. The resulting array contains numbers and blanks, and AVERAGE calculates the mean of the numbers only.
  • Example: If A2:A10 contains {10, #N/A, 20, 30, #N/A, 40, 50, #N/A, 60}, the formula returns 35 (sum of 10+20+30+40+50+60 = 210, divided by 6).
  • Note: In Excel 2021 or 365, you can simply press Enter; in Excel 2019 or earlier, press Ctrl+Shift+Enter to create an array formula.

Solution 2: AGGREGATE Function (Non-Array)

AGGREGATE is a robust function that can ignore errors, hidden rows, or both. It works in all modern Excel versions without array entry.

  • Assumption: Values in A2:A10.
  • Formula: =AGGREGATE(1,6,A2:A10)
  • Explanation: The first argument 1 means AVERAGE. The second argument 6 means “ignore error values”. The third argument is the range. This returns the average of all numeric cells, ignoring errors and text.
  • Example: With the same data as above, the formula returns 35.
  • Advantage: No Ctrl+Shift+Enter needed, works in all versions, and also ignores text and logical values.

Handling Specific Criteria with Errors

If you need to average cells that meet a condition (e.g., only values greater than 10) while ignoring errors, you can combine AGGREGATE with IFERROR in an array formula, or use AVERAGEIFS on a helper column. The array approach: =AVERAGE(IFERROR(IF(A2:A10>10,A2:A10,""),"")) entered with Ctrl+Shift+Enter (or Enter in 365). This first checks the condition, then converts errors to blanks. Alternatively, create a helper column with =IFERROR(A2,"") and then use AVERAGEIF on that helper column with your criteria. For example, if B2:B10 contains the IFERROR results, use =AVERAGEIF(B2:B10,">10").

Common Problems and Misconceptions

One common mistake is using =AVERAGEIF(A2:A10,"<>#N/A")—this does not work because #N/A is an error, not text. Another is using =AVERAGE(IF(ISERROR(A2:A10),"",A2:A10)) without array entry in older Excel, which gives a #VALUE! error. Also, note that AVERAGEIF ignores cells that contain text, but errors are not text—they are error values. If you have a mix of errors and text, AGGREGATE ignores both, but AVERAGE with IFERROR only converts errors to text, which AVERAGE then ignores—so both work. However, if you have formulas that return empty strings (“”), AVERAGEIF will treat them as text and ignore them, but AVERAGE with IFERROR will also ignore them because they are text. The key is that errors must be explicitly handled.

Comparing AVERAGEIF, AVERAGEIFS, and AGGREGATE

Here is a quick comparison for handling errors:

  • AVERAGEIF: Ignores text and blanks, but not errors. Cannot exclude errors via criteria.
  • AVERAGEIFS: Same limitation—errors in the average range cause #N/A.
  • AGGREGATE: Can ignore errors, hidden rows, and nested subtotals. Option 6 ignores errors, option 5 ignores hidden rows, option 7 ignores both.
  • AVERAGE with IFERROR: Converts errors to text, which AVERAGE ignores. Works as array formula in older versions.

For most cases, AGGREGATE is the simplest and most reliable. If you need to use AVERAGEIF for criteria, consider using a helper column to clean errors first.

Frequently Asked Questions

Q: Can I use AVERAGEIF to ignore #N/A by using “<>#N/A” as criteria?
No, that criteria compares text, not error values. #N/A is an error, not text, so the condition never matches, and the function returns #N/A.

Q: Does AVERAGEIF ignore cells with errors if I don’t specify any criteria?
No, if you use AVERAGEIF with a criteria like “>0”, errors still cause the function to return #N/A. Errors are never ignored by AVERAGEIF or AVERAGEIFS.

Q: What is the difference between AGGREGATE(1,6,…) and AGGREGATE(1,7,…)?
Option 6 ignores error values only. Option 7 ignores both error values and hidden rows. If you have filtered rows and want to exclude them, use 7; otherwise 6 is fine.

Q: In Excel for Mac, does the array formula work the same?
Yes, but you press Command+Shift+Enter instead of Ctrl+Shift+Enter. In Excel for Microsoft 365 on Mac, dynamic arrays work automatically, so you can just press Enter.

Q: How do I average ignoring errors but also excluding zeros?
Use =AGGREGATE(1,6,IF(A2:A10<>0,A2:A10,"")) as an array formula (Ctrl+Shift+Enter in older versions). Alternatively, use a helper column with =IF(AND(ISNUMBER(A2),A2<>0),A2,"") and then AVERAGE that column.

Q: Can I use AVERAGEIF with a wildcard to ignore errors?
No, wildcards only work with text criteria, not error values. Errors are not text, so they are not matched.

Alternative: Using IF and ISERROR

Another classic approach is to use an array formula with IF and ISERROR: =AVERAGE(IF(ISERROR(A2:A10),"",A2:A10)). This works similarly to IFERROR but is more verbose. In Excel 365, you can use the shorter IFERROR version. In older versions, both require Ctrl+Shift+Enter. This method is useful if you need to perform additional checks within the IF function, such as excluding specific error types like #DIV/0! only.

For example, to ignore only #N/A but not other errors, you could use =AVERAGE(IF(ISNA(A2:A10),"",A2:A10)). This is more precise but rarely needed.

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.