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
SUMIFSwithIFERRORinside 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
- Excel Print Merge File Showing X? Fixes
- Excel Profit and Loss Template: Free Options
- Free Excel Project Timeline Templates: How to Get One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNTIFS With Multiple Criteria in One RangeYes. Excel’s COUNTIFS can apply more than one condition to the same range by repeating that range for…
- How to Filter in Excel With Multiple CriteriaTo filter data by more than one condition in Excel, turn on filters for the full data range,…
- Excel SUMIFS Not Equal: Criteria and FixesTo sum values that do not match a criterion, use the not-equal operator <> in SUMIFS. For example,…
- LARGE and SMALL Excel Formulas: Find Ranked ValuesUse Excel’s LARGE and SMALL functions to return the value at a chosen rank in a set of…
- Excel TODAY Formula for Days Between DatesTo calculate how many days have passed from a date in cell A2 to today, enter =TODAY()-A2 in…
- Excel Sum of Filtered Data: SUBTOTAL, AGGREGATE & FILTERTo sum only the rows currently visible after filtering in Excel, put =SUBTOTAL(9,C2:C7) in a cell outside the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.