Sum and Ignore Errors in Excel: Formula & Fixes

This guide explains how to sum a range in Excel while ignoring error values like #N/A, #DIV/0!, and #VALUE!. You’ll learn the exact formulas, how they work, and when to use each one, so you can get a total even when some cells are broken.

Formula

The most reliable formula to sum a range while ignoring errors is an array formula that checks each cell for an error and treats errors as 0:

=SUM(IF(ISERROR(range), 0, range))

In older Excel versions (pre–365), you must enter this as an array formula by pressing Ctrl+Shift+Enter. In Excel 365 and Excel 2021, it works as a normal formula because of dynamic arrays.

Variables:

• range – the cells you want to sum (e.g., A1:A10).
• ISERROR(range) – returns TRUE for any cell containing any error type.
• IF(ISERROR(range), 0, range) – replaces each error with 0, keeping valid numbers.
• SUM(…) – adds up the resulting array.

Worked Example

Suppose your range A1:A10 contains: 5, #N/A, 10, #DIV/0!, 15, 20, #VALUE!, 25, 30, 35. The formula =SUM(IF(ISERROR(A1:A10), 0, A1:A10)) evaluates as:

1. ISERROR(A1:A10) returns {FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}
2. IF converts errors to 0: {5;0;10;0;15;20;0;25;30;35}
3. SUM adds: 5+0+10+0+15+20+0+25+30+35 = 140

The calculator above uses the same logic and returns 140 for the default range if you replace it with the example values.

Why Do Errors Break a Normal SUM?

A regular =SUM(A1:A10) will return an error if any cell in the range contains an error. This is by design – Excel wants to alert you that the total is incomplete. But in real work, you often have lookup formulas (like VLOOKUP) that return #N/A for missing items, and you still need a total of the valid numbers.

Common error types you might encounter:

• #N/A – “value not availableâ€� (typical from VLOOKUP/XLOOKUP)
• #DIV/0! – division by zero
• #VALUE! – wrong data type
• #REF! – invalid cell reference

Alternative Formulas: AGGREGATE and IFERROR

Excel’s AGGREGATE function was designed for this. It can ignore errors, hidden rows, and more. The simplest form is:

=AGGREGATE(9, 6, range)

• 9 = SUM function number.
• 6 = ignore error values option.
• range – your data.

This works in Excel 2010 and later, and requires no array entry. It is often the cleanest solution.

Another common approach is to wrap each cell with IFERROR before summing, but that requires a helper column or a more complex array formula. For example:

=SUM(IFERROR(A1:A10, 0)) – in Excel 365 this works as a dynamic array; in older versions you need Ctrl+Shift+Enter. This is shorter than the ISERROR version but does the same thing.

How to Choose the Right Method

Your choice depends on your Excel version and your need for flexibility:

• AGGREGATE – best for most users; works in Excel 2010+ without array entry.
• SUM(IFERROR(…)) – easy to remember and works in Excel 365/2021.
• SUM(IF(ISERROR(…))) – classic array formula; still works in all versions.

If you need to ignore only specific errors (e.g., ignore #N/A but not #DIV/0!), use SUMIF with a condition like <>#N/A, but that gets messy. AGGREGATE is simpler.

Common Mistakes and Limitations

• Forgetting Ctrl+Shift+Enter in older Excel – the formula will return #VALUE! or a single value instead of the sum.
• Using ISERROR on a whole range – it works, but it’s an array operation; if you use it in a single cell without array entry, it won’t sum correctly.
• Hidden rows – AGGREGATE with option 5 ignores hidden rows, but option 6 does not. Choose the right option number.
• Text values – these formulas ignore text as well, which is usually fine, but if you need to count text, use a different function.

The formula only ignores errors; it does not fix them. You may still want to find and correct the source of the errors.

Frequently Asked Questions

Why does my SUM still return an error even with IFERROR?

If you used =SUM(IFERROR(A1:A10,0)) in an older Excel version without pressing Ctrl+Shift+Enter, it will not work as expected. Also, if your range contains a formula that returns an error as text (e.g., the formula itself is text), IFERROR won’t catch it. Make sure the cells contain real errors, not text.

Can I ignore errors in a filtered list?

Yes, use AGGREGATE with option 5 (ignore hidden rows) and 6 (ignore errors). The syntax is =AGGREGATE(9,5,range) – but note that option 5 ignores hidden rows, not just filtered ones. If you need to ignore only filtered-out rows, use SUBTOTAL, but SUBTOTAL does not ignore errors.

What if I want to ignore only #N/A errors, not others?

You can use =SUMIF(range, "<>#N/A", range) but this will also ignore any text values. A better approach is to use SUM(IF(ISNA(range),0,range)) as an array formula – this only ignores #N/A.

Does AGGREGATE work in Excel 2007?

No, AGGREGATE was introduced in Excel 2010. For Excel 2007, use the array formula =SUM(IF(ISERROR(range),0,range)) with Ctrl+Shift+Enter.

Can I use these formulas in Google Sheets?

Yes, Google Sheets supports IFERROR and SUM, but not AGGREGATE. Use =SUM(IFERROR(A1:A10,0)) as a regular formula – no array entry needed.

Why does the calculator above show a different result than my Excel?

The calculator uses the same logic but only on the numbers you enter. If your Excel range contains formulas that return errors, the calculator can’t see them – you need to enter the actual values. Also, check that you’ve entered the range correctly in the calculator.

Related

• Apple Watch SE 3 New Look: Design, Rumors, and Facts
• Split Screen Vizio TV: How to Set It Up
• Move Apps & Files to SD Card on Android: What Works

in r | Range | - | A1:A10 | 0
out sum | Sum ignoring errors | - | sum(if(iserror(r),0,r)) | 0