Excel SUMIF Ignore Errors: Formula & Fixes

Formulas & functions · Updated November 22, 2026

The direct answer: Excel’s SUMIF function does not have a built-in option to ignore errors, but you can make it ignore errors by using SUMIF with a wildcard criteria like "<>#N/A" for specific error types, or by combining SUM with IFERROR in an array formula (e.g., =SUM(IFERROR(range,0))). For a robust solution that ignores all errors, use =SUM(IF(ISERROR(range),0,range)) entered as an array formula (Ctrl+Shift+Enter in older Excel, or just Enter in Excel 365).

Why SUMIF Fails on Errors

In Excel, any cell containing an error value (like #DIV/0!, #N/A, #VALUE!) is treated as a special value. When SUMIF evaluates a range that includes an error, it immediately returns an error—even if the criteria would exclude that cell. This is because SUMIF checks the criteria against each cell, but if any cell in the sum_range contains an error, the function propagates that error. The same behavior applies to SUMIFS, COUNTIF, and AVERAGEIF.

For example, if you have numbers in A1:A5 and one cell (say A3) contains #DIV/0!, then =SUMIF(A1:A5,">0") will return #DIV/0! instead of the sum of positive numbers. This is a common frustration because the error is often in a cell that logically shouldn’t affect the sum.

How to Use SUMIF to Ignore Specific Errors

You can make SUMIF ignore a specific error type by using a criteria that excludes it. For instance, to sum all numbers except those that are #N/A, use:

=SUMIF(range, "<>#N/A", sum_range)

This works because the criteria "<>#N/A" means “not equal to the #N/A error”. However, this only ignores #N/A; other errors like #VALUE! or #REF! will still cause the formula to fail. To ignore multiple specific errors, you’d need to nest multiple SUMIFs, which gets messy. For example, to ignore both #N/A and #DIV/0!:

=SUMIF(range, "<>#N/A", sum_range) + SUMIF(range, "<>#DIV/0!", sum_range)

But this double-counts cells that are neither error, so it’s not a practical solution. A better approach is to use a helper column or an array formula.

The Robust Solution: Use SUM with IFERROR or ISERROR

The most reliable way to ignore all errors in a sum is to use the SUM function combined with IFERROR or ISERROR. Here are the two main approaches:

  • Using IFERROR (Excel 2007 and later): =SUM(IFERROR(A1:A10,0)) — This converts each error to 0 and sums the rest. In Excel 365 and Excel 2021, this works as a dynamic array formula and you just press Enter. In older Excel versions, you must press Ctrl+Shift+Enter to enter it as an array formula.
  • Using ISERROR (all versions): =SUM(IF(ISERROR(A1:A10),0,A1:A10)) — This checks each cell; if it’s an error, use 0, otherwise use the value. Again, in older Excel, press Ctrl+Shift+Enter.

For a non-array alternative, you can use SUMPRODUCT with ISERROR: =SUMPRODUCT(IFERROR(A1:A10,0)) — but note that SUMPRODUCT with IFERROR also requires array entry in older versions. The simplest is to use SUM with IFERROR in Excel 365, as it spills automatically.

How to Choose the Right Method

Your choice depends on your Excel version and whether you need to ignore all errors or specific ones.

  • If you use Excel 365 or Excel 2021: Use =SUM(IFERROR(range,0)) — it’s clean and doesn’t require special key combinations.
  • If you use Excel 2019 or earlier: Use =SUM(IF(ISERROR(range),0,range)) and press Ctrl+Shift+Enter. You’ll see curly braces {} around the formula.
  • If you only want to ignore #N/A errors (e.g., from VLOOKUP): Use =SUMIF(range, "<>#N/A", sum_range) — this is simpler and doesn’t require array entry.
  • If you want to ignore errors but also apply multiple criteria: Use SUMIFS with IFERROR inside an array formula, e.g., =SUM(IFERROR(SUMIFS(sum_range, criteria_range1, criteria1),0)) — but this is complex; consider a helper column instead.

Common Problems and Misconceptions

Misconception: SUMIF has an “ignore errors” option. It doesn’t. Many users search for a setting or checkbox, but there is none. The only way is to modify the formula or the data.

Problem: Array formulas not working. If you enter =SUM(IFERROR(A1:A10,0)) in Excel 2019 and press just Enter, you’ll get a #VALUE! error or a single value that’s wrong. You must press Ctrl+Shift+Enter. In Excel 365, you don’t need to, but if you open the file in an older version, it may break.

Problem: Errors in criteria range. If the criteria range itself contains errors, SUMIF will also fail. For example, =SUMIF(A1:A10, ">0", B1:B10) will error if any cell in A1:A10 has an error. The same solutions apply: use SUM(IF(ISERROR(A1:A10),0,IF(A1:A10>0,B1:B10,0))) as an array formula.

Problem: Blank cells vs. zero. IFERROR treats blanks as 0, which is fine for summing. But if you want to ignore blanks as well, use SUM(IF(ISNUMBER(A1:A10),A1:A10,0)) — this sums only numeric cells, ignoring errors, text, and blanks.

Comparison: SUMIF vs. SUM+IFERROR

Here’s a quick comparison to help you decide:

  • SUMIF with wildcard: Only ignores specific error types, not all. Simple but limited.
  • SUM+IFERROR array: Ignores all errors, works with any criteria (if you add IF statements). Requires array entry in older Excel.
  • SUMPRODUCT with IFERROR: Similar to SUM+IFERROR but can handle arrays without Ctrl+Shift+Enter in some cases (though not recommended for large ranges).
  • Helper column: Use =IFERROR(A1,0) in a helper column, then sum that column. This is the easiest for beginners and works in all versions.

For example, if you have sales data in B2:B100 and some cells have errors, you could add a helper column C with =IFERROR(B2,0) and then sum column C. This is transparent and easy to audit.

Frequently Asked Questions

Q: Can I make SUMIF ignore all errors without using array formulas?
A: Yes, use a helper column. In a new column, enter =IFERROR(A1,0) and copy down. Then use =SUM(helper_range). This works in all Excel versions and is easy to understand.

Q: Why does my SUMIF return #N/A even when the criteria doesn’t match the error cell?
A: Because SUMIF evaluates the entire range. If any cell in the sum_range contains an error, the function returns that error, regardless of criteria. This is by design in Excel.

Q: What if I want to ignore errors but also sum only values greater than 100?
A: Use an array formula: =SUM(IF(ISERROR(A1:A10),0,IF(A1:A10>100,A1:A10,0))) and press Ctrl+Shift+Enter in older Excel. In Excel 365, just Enter.

Q: Does this work in Google Sheets?
A: Yes, the same formulas work in Google Sheets, but array formulas are entered with Ctrl+Shift+Enter (or just Enter in newer versions). Google Sheets also has IFERROR and ISERROR.

Q: Is there a way to ignore errors in SUMIFS with multiple criteria?
A: Yes, wrap the SUMIFS in IFERROR: =SUM(IFERROR(SUMIFS(sum_range, crit_range1, crit1, crit_range2, crit2),0)) as an array formula. However, this only ignores errors if the entire SUMIFS returns an error, not if individual cells have errors. For per-cell error handling, use a helper column or array formula with ISERROR.

Q: Can I use AGGREGATE to ignore errors?
A: Yes, =AGGREGATE(9,6,range) sums ignoring errors (option 6). This is a great non-array alternative available in Excel 2010 and later. It’s often the simplest solution.

Final Thoughts and Best Practice

For most users, the best practice is to avoid errors in the first place by using IFERROR in the formulas that generate the data. For example, if you have a VLOOKUP that might return #N/A, wrap it: =IFERROR(VLOOKUP(...),0). This keeps your data clean and makes SUMIF work normally. If you’re dealing with imported data that already contains errors, use AGGREGATE or the array formula with IFERROR. Remember to always test your formulas on a small sample to ensure they return the expected result.

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.