Sum and ignore errors

In this example, the goal is to create a formula that will sum values in a range that may contain errors. A common problem in Excel is that errors in data show up in the results of other formulas. For example, in the worksheet shown, the SUM function is used to sum the named range data (D5:D15) . Because the range D5:D15, the SUM function itself returns #N/A. The formula in cell F5 is:

=SUM(data) // returns #N/A

Ideally, the errors can be resolved by entering the missing data, and the SUM function will start working again. In fact, it is often helpful when summary calculations display errors, because it signals there are problems in the data that should be investigated. However, there are situations where you want to ignore errors and sum the available numbers. In this article, we look at three different formula options.

SUMIF

One option is to use the SUMIF function with the not equal to (<>) operator like this:

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

This is a relatively simple formula and it works fine as long as the range contains only #N/A errors. SUMIF returns the sum of values not equal to #N/A. However, if another type of error occurs, the SUMIF function will itself return an error. For example if the #DIV/0! error appears in the data, SUMIF will return #DIV/0!.

AGGREGATE

Another more robust option is to use the AGGREGATE function. In cell F7, AGGREGATE is configured to sum and ignore errors by setting function_num to 9, and options to 6:

=AGGREGATE(9,6,data) // sum and ignore errors

The AGGREGATE function is a multipurpose function that can run other functions like SUM, COUNT, AVERAGE, MAX, etc. with special behaviors. For example, AGGREGATE can optionally ignore errors, hidden rows, and even other calculations. This formula will ignore all errors that might appear in data, not just the #N/A error. AGGREGATE can run 19 functions total, see this page for a full explanation.

SUM and IFERROR

Finally, we can create a more literal array formula using the SUM function together with the IFERROR function. In cell F8, we nest the IFERROR function inside SUM like this:

=SUM(IFERROR(data,0)) // sum and ignore errors

Note: this is an array formula and must be entered with Control + Shift + Enter, except in Excel 365, where dynamic arrays are native.

In this formula, the IFERROR function is used to trap errors and convert them to zero. In the example shown, the named range data contains eleven cells, which can be represented as an array like this:

{20;21;10;39;#N/A;28.5;5.5;12.5;10;6;#N/A} // the range D5:D15

IFERROR converts the #N/A errors to zero:

=IFERROR(data,0)
=IFERROR({20;21;10;39;#N/A;28.5;5.5;12.5;10;6;#N/A},0)
={20;21;10;39;0;28.5;5.5;12.5;10;6;0}

The resulting array is returned directly to the SUM function:

=SUM({20;21;10;39;0;28.5;5.5;12.5;10;6;0}) // returns 152.5

and SUM returns 152.5 as the final result.

Note: Use caution when ignoring errors. Suppressing errors can be dangerous because it hides underlying problems.