Exceljet

Quick, clean, and to the point

Sum and ignore errors

Excel formula: Sum and ignore errors
Generic formula 
=AGGREGATE(9,6,data)
Summary 

To sum a range of cells and ignore errors, you can use the AGGREGATE function. In the example shown, the formula in F7 is:

=AGGREGATE(9,6,data)

where data is the named range D5:D15. The #N/A errors are ignored and the result is the sum of numbers in D5:D15. See below for other formula options.

Explanation 

One common problem in Excel is that errors in data show up in summary calculations. For example, in the worksheet shown, the formula below returns #N/A because the named range data (D5:D15) contains #N/A errors:

=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. 

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.