Excel SUM Ignore Errors: Formulas for Clean Totals

Formulas & functions · Updated November 18, 2026

Yes. To sum numbers while treating error cells as zero in Microsoft Excel for Microsoft 365, use =SUM(IFERROR(B2:B6,0)); alternatively, use =AGGREGATE(9,6,B2:B6) to have Excel ignore errors in the referenced range. Both return a total from the remaining numeric values, but they work differently and suit slightly different situations.

Why SUM Returns an Error

Excel’s SUM function adds numbers, but it does not automatically skip error values. If even one cell in the range contains an error such as #N/A, #VALUE!, #DIV/0!, or #REF!, a formula such as =SUM(B2:B6) returns an error instead of a partial total. With B2:B6 containing 120, #N/A, 85, #DIV/0!, and 45, respectively, that formula returns an error, not 250.

This behavior is different from how SUM treats blanks and text. In a referenced range, SUM normally ignores empty cells and text entries, including numbers stored as text. Errors are not treated as text or blanks: they signal that a calculation or reference has a problem, and that problem flows into the total unless you handle it explicitly.

That distinction matters when a report contains a mixture of valid results and errors. You may want a temporary total while investigating the source, or a dashboard that continues to show the usable values. An error-tolerant sum can help, but it does not repair the underlying errors or explain why they occurred.

Use IFERROR to Replace Errors with Zero

For a straightforward total, wrap the range in IFERROR and replace any error with zero. With B2:B6 containing 120, #N/A, 85, #DIV/0!, and 45, enter this formula in another cell; it returns 250:

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

IFERROR checks each value supplied from the range. A valid number passes through unchanged, while an error becomes 0; SUM then adds the resulting values. Zero is useful because adding it does not change the total. The formula leaves the original cells alone, so you can still inspect or correct their errors.

This approach is easy to adapt to a different range. For example, if the values are in C2:C100, change the range in the formula to C2:C100. Keep the replacement value as 0 when the goal is to exclude errors from the total. Replacing errors with another number, such as 1, would change the total and should only be done if that replacement has a deliberate meaning.

In Excel for Microsoft 365, the formula handles the range as an array without a special keystroke. It also works in current Excel for Mac and Excel for the web. In older desktop Excel versions without dynamic-array behavior, entering a formula that processes a whole range this way may require Ctrl+Shift+Enter; if you share workbooks with older versions, check how the formula behaves there.

Use AGGREGATE to Ignore Errors in a Range

AGGREGATE offers another direct option. Its function number 9 means SUM, and option 6 tells it to ignore error values. With the same B2:B6 values—120, #N/A, 85, #DIV/0!, and 45—this formula returns 250:

=AGGREGATE(9,6,B2:B6)

The arguments are, in order, the calculation to perform, the behavior options, and the range to calculate. AGGREGATE is available in Excel 2010 and later, including Microsoft 365. In this example, option 6 skips error values but does not mean that hidden rows are skipped. If you need to ignore both errors and manually hidden rows, option 7 is the relevant option:

With the same B2:B6 layout, this version returns 250 when errors are present, and it also excludes values in manually hidden rows:

=AGGREGATE(9,7,B2:B6)

Be careful about what counts as hidden: AGGREGATE’s option 7 is for manually hidden rows, not a general instruction to exclude every row hidden by an AutoFilter. If visibility matters to the report, test the formula against the way the rows are hidden and the result you expect.

How to Choose Between the Two Methods

For most ordinary totals, IFERROR is clear and flexible: it makes the rule explicit that an error should contribute zero, and it can be combined with other calculations. AGGREGATE is compact when the job is simply to perform a supported calculation while ignoring errors. The choice depends on readability, compatibility, and whether you also need AGGREGATE’s other options.

  • Choose SUM with IFERROR when you want to see exactly how errors are being handled or expect to adapt the formula later.
  • Choose AGGREGATE when you want a concise sum that skips errors, or when you need its option for ignoring manually hidden rows as well.
  • Fix the source instead when an error indicates missing data, a broken reference, or an invalid calculation that should not be silently excluded.

Both example formulas return the same total for the five stated values: 120 + 85 + 45 = 250. Neither method changes the cells in B2:B6. They only change how the total formula treats those cells while calculating its result.

Set Up and Check the Formula

To use either method in Excel for Microsoft 365 on Windows:

  1. Identify the exact numeric range you intend to total, and check that it does not include a label or an unrelated value.
  2. Select an empty cell where the result should appear.
  3. Type the IFERROR or AGGREGATE formula, replacing the example range with your own, and press Enter.
  4. Compare the answer with a small manual check or a filtered view of the valid numbers, especially if the result feeds a report.
  5. Investigate the error cells separately if they could indicate missing or incorrect source data.

The formulas and steps are the same in Excel for Mac and Excel for the web. If a regional setting on your computer uses semicolons as formula separators, Excel may require semicolons in place of the commas shown here. Use the separator accepted by your installation; do not change the function names or the order of the arguments.

Common Mistakes and Misconceptions

The most common misconception is that SUM skips everything it cannot add. It generally ignores text and blanks in a cell reference, but it does not ignore errors. A number that looks numeric but is stored as text is a different issue from a cell containing an error, so an error-handling wrapper is not a universal fix for numbers stored as text.

Another frequent mistake is using an error-tolerant total to make a workbook appear healthy. If a cell shows #DIV/0!, for example, the calculation may be dividing by an empty or zero denominator. Ignoring that result can be appropriate for a summary, but it can also conceal a data-quality issue. Keep the source error visible or document the reason for excluding it when others rely on the workbook.

Also confirm the range. If new rows are added below B6, a formula that still refers to B2:B6 will not include them. An Excel Table with a structured reference can expand more naturally as rows are added, but the same principle applies: the total should reference the intended data and not accidentally omit later entries. Finally, do not put the total formula inside the range it sums; including its own cell can create a circular reference.

Handle Only the Errors You Intend to Ignore

IFERROR replaces any error with the chosen fallback, not just one particular error type. That includes errors that may reveal a real problem, such as a deleted reference or a failed lookup. AGGREGATE’s error-ignoring option also excludes errors without distinguishing whether they are harmless or significant. Neither formula can determine whether an error is safe to omit; that decision belongs to the workbook’s logic.

If only one kind of error is expected, consider whether the original calculation can be written to handle that condition more accurately. For example, a formula that divides by a value can be designed to return a clear blank or message when the denominator is zero. This can make the workbook’s behavior easier to understand than treating every possible error as zero in the final total. Do this only when the alternative result matches the purpose of the calculation.

For recurring reports, consider displaying the total alongside a separate indication that errors exist, rather than hiding the fact that some inputs were excluded. That way readers can use the usable subtotal without mistaking it for a complete, fully validated total. The best choice depends on whether the worksheet is exploratory, operational, or used for formal reporting.

Frequently Asked Questions

Does SUM ignore errors by default?
No. SUM generally ignores blanks and text in a referenced range, but an error in the range causes the result to be an error. Use IFERROR or AGGREGATE when you intentionally want to exclude errors from the calculation.

What is the simplest formula to sum while ignoring errors?
In Microsoft 365, =SUM(IFERROR(B2:B6,0)) is a clear option. It treats each error in B2:B6 as zero and adds the remaining numeric values.

Does AGGREGATE ignore errors and hidden rows?
It depends on the option argument. Option 6 ignores errors; option 7 ignores errors and manually hidden rows. Check how filtered or hidden rows should be treated before relying on the result.

Will IFERROR hide errors in the original cells?
No. IFERROR inside the total affects the calculation’s result, not the contents of the source cells. The original errors remain visible in their cells unless you separately change those formulas or values.

Why does my SUM still show an error?
The formula may not include IFERROR or an error-ignoring AGGREGATE option, or the error may come from outside the range you meant to handle. Check the actual referenced cells and confirm that the formula uses the intended range.

Should I ignore errors or fix them?
Ignore them only when excluding those values is appropriate for the purpose of the total. If an error signals a missing input, incorrect formula, or broken reference, investigate and correct the source rather than relying on a partial total alone.

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.