Sum a Range with Errors in Excel

Add to Favorites

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.

f8d8d9b831781ad54c057517237540a0.png

Result:

8c1b03084a6a187011e91b98c290f048.png

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.

f22364c31a65a2bc946326f8dc8d53e7.png

All of the ISERROR values have been checked:

0101f02f754333cdbf26837e1aba6816.png

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:

39044ca78fc8dafcf4036621d02d8fb0.png

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

86ce68c62702f031946e0ab63470d43c.png

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.


Excel Function: IF(), ISERROR(), SUM()
Downloadable Files: Excel File

Similar Content on TeachExcel
Limit the Total Amount a User Can Enter into a Range of Cells in Excel
Tutorial: How to limit the amount that a user can enter into a range of cells in Excel.  This works ...
Loop through a Range of Cells in Excel VBA/Macros
Tutorial: How to use VBA/Macros to iterate through each cell in a range, either a row, a column, or ...
Count The Number of Words in a Cell or Range of Cells in Excel - With User-Specified Delimiter / Separator - UDF
Macro: UDF to count the number of words in a cell or range with a user-specified delimiter. ...
Remove Vlookup #N/A Error in Excel
Tutorial: How to remove the #N/A error from Vlookup and replace it with a friendly message or a bl...
Require a Unique List of Numbers in a Range in Excel
Tutorial: I'll show you how to require a user to enter a unique number into a range of cells in Exce...
Count The Number of Words in a Cell or Range of Cells in Excel - UDF
Macro: Count words in cells with this user defined function (UDF). This UDF allows you to count t...