Sum a Range with Errors in Excel

How to sum a range of cells that contains errors.

This requires an array formula, so it will take a few steps, but it's not too difficult.

Sections:

Formula

Formula Explanation

Notes

Formula

=SUM(IF(ISERROR(A1:A6),0,A1:A6))

Array Formula - this is an array formula so you must enter it using Ctrl + Shift + Enter.

A1:A6 is the range that you want to SUM. Make sure to change it in both parts of the formula to work with your data.

Result:

Notice the curly braces placed around the formula in the formula bar; that means that this is an array formula. If you type these curly braces in by hand, it does not make the formula an array formula, you must still use Ctrl + Shift + Enter when inputting the formula in order to do that.

Formula Explanation

First, the formula is an Array Formula, which means that it will perform multiple actions against a range of cells. As such, you must input the formula using Ctrl + Shift + Enter instead of just hitting Enter. If you just hit Enter to input the formula, it will not work or it will give you an inaccurate result.

Now, let's dissect the formula; as always with formulas, we start from the inside.

ISERROR() function figures out if there is an error in a cell or not and returns TRUE or FALSE. A range, A1:A6, is used in this function because this is an array formula and so it works on the entire range instead of a single cell.

IF() function gets the TRUE or FALSE value returned from the ISERROR function; if it gets TRUE, the IF statement outputs a 0 (zero); if it gets FALSE, the IF statement outputs the value from that cell. The argument for the FALSE result is a range, A1:A6, and this may seem confusing but that is because this is an array formula and so it works on the entire range.

SUM() function is finally what adds up all of the numbers that are returned by the IF statement.

Array Formula

This formula looks a little funny because it is an array formula.

This is why there are range references, A1:A6, instead of a reference to a single cell within the ISERROR and IF functions.

An array formula can do on a range what would otherwise happen with a single cell, such as to check if there is an error in the cell or not.

If you run the formula through the Evaluate Formula feature (Formulas tab > Evaluate Formula), you can see how it performs each operation on all of the cells in the range and then returns the result back to the sum function inside of an array.

In the following images, the underlined portion is what will be run next.

All of the ISERROR values have been checked:

Then, the IF statement was run on all of those TRUE FALSE values and returned the corresponding number for each one back to the SUM function:

Now the SUM function works on the array to add the numbers together and get the final result:

You can learn more about the Evaluate Formula feature in our tutorial on it.

Notes

This is a rather simple array formula and all you need to do to get it to work for you is to copy the formula above and change the range references to fit your data. The main thing to remember is that you must input array formulas using Ctrl + Shift + Enter.

Make sure to download the sample file for this tutorial to work with this example in Excel.