How to Sum and Ignore Errors in Excel

To sum and ignore errors in Excel, use =AGGREGATE(9,6,B2:B7) or =SUM(IFERROR(B2:B7,0)). A plain SUM stops at the first error and shows it as the result, so one #N/A from a lookup or one #DIV/0! breaks the whole total. This guide shows three formulas that skip errors, when to use each, and how to find the errors so you don't hide a real problem.

Why SUM returns an error#

SUM adds numbers, and it skips text and blank cells. But if any cell in its range holds an error value, such as #N/A, #DIV/0! or #VALUE!, SUM returns that error instead of a total. Here is a store report where two cells failed:

=SUM(B2:B7) returns #DIV/0!, not a number. The total of the four good cells is 4,400 (1,250 + 980 + 1,410 + 760). Every method below returns 4,400. If SUM itself is new to you, start with the SUM function guide or the article on using SUM in Excel.

Method 1: Sum and ignore errors with AGGREGATE#

AGGREGATE does SUM, AVERAGE, MAX and more, and it can skip errors. The syntax is:

=AGGREGATE(function_num, options, ref1, ...)

For a sum that ignores errors:

=AGGREGATE(9,6,B2:B7)

• 9 tells AGGREGATE to SUM.
• 6 tells it to ignore error values.
• B2:B7 is the range.

The result is 4,400. AGGREGATE works in Excel 2010 and later, on Windows and Mac. Excel shows a list of function numbers and options as you type, so you don't have to remember them.

Two other options are worth knowing. Option 7 ignores errors and hidden rows, which is useful on a filtered list. Option 5 ignores hidden rows only. Change the first number to get other results: 1 for AVERAGE, 4 for MAX, 5 for MIN. For example, =AGGREGATE(4,6,B2:B7) returns the highest sale, 1,410.

AGGREGATE has one limit to know. With a range like B2:B7, it skips errors and, with option 7, hidden rows. But if you give it a calculation instead of a range, such as B2:B7*2, only some of its functions accept that. For a plain total of a column, the form shown here always works.

Method 2: Sum and ignore errors with SUM and IFERROR#

Wrap the range in IFERROR to turn each error into 0, then add:

=SUM(IFERROR(B2:B7,0))

IFERROR checks each cell in B2:B7. Numbers pass through unchanged, and errors become 0. SUM then adds the clean list and returns 4,400. The IFERROR function guide explains the two arguments.

In Excel 2021 and Microsoft 365, press Enter as usual. In Excel 2019 and earlier, this is an array formula, so confirm it with Ctrl+Shift+Enter. If you press plain Enter in an older version, you may get the wrong answer or an error.

To skip only #N/A and still see other errors, use IFNA instead: =SUM(IFNA(B2:B7,0)). On this data it still returns #DIV/0!, because IFNA doesn't catch that error. That is useful when a missing lookup value is expected but a division error is a real bug.

Method 3: The SUMIF trick for any version#

SUMIF only adds cells that meet its criteria, and an error never meets a number test. So a criteria that every normal number passes will skip all errors:

=SUMIF(B2:B7,"<9.99E+307")

9.99E+307 is close to the largest number Excel can store, so every real number is smaller. The result is 4,400, with no special keys and in every version of Excel. The downside is that the formula is hard to read, so add a note for whoever opens the file next.

Which method should you use?#

All three give the same total. Pick by version and by what else you need.

SUBTOTAL is not on the list. It ignores hidden rows but not errors, so one error still breaks it. The comparison of SUBTOTAL vs SUM explains what SUBTOTAL does skip. For a total of only the rows a filter shows, see how to filter and sum.

How to fix errors at the source#

Skipping errors in the total is quick, but the errors are still in the sheet. Often it is better to stop them appearing. If column B is filled by formulas, wrap each one:

=IFERROR(VLOOKUP(A2,Prices!A:B,2,FALSE),0)

Now each row shows 0 instead of #N/A, and a plain SUM works. Use this only when 0 is a true answer. If a store's sales are unknown, a blank ("") is more honest than 0, and SUM skips blanks too.

The same idea applies across a wider sheet or several sheets. The guides to summing multiple columns and summing across sheets work with any of these methods.

How to count the errors you skipped#

A total that quietly leaves out two stores can mislead as much as an error. Count the errors next to the total:

=SUMPRODUCT(--ISERROR(B2:B7))

ISERROR returns TRUE for each error. The double minus (--) turns TRUE into 1 and FALSE into 0, and SUMPRODUCT adds them. The result is 2. A label such as "Total 4,400 (2 stores missing)" tells readers exactly what they are looking at.

To find the error cells themselves, use Home > Find & Select > Go To Special, choose Formulas, and tick only Errors. Excel selects every formula cell that shows an error.

Practise it#

Practise IFERROR in a live spreadsheet: you wrap formulas that fail and the sheet checks your answer instantly. Then try the exercise on this page, which totals a column with two errors, and review the basics in the SUM lesson.