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.

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

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.

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.

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.

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

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

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

Macro: UDF to count the number of words in a cell or range with a user-specified delimiter. ...

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

Tutorial: I'll show you how to require a user to enter a unique number into a range of cells in Exce...

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

Tutorial: How to remove the #N/A error from Vlookup and replace it with a friendly message or a bl...

Loop through a Range of Cells in a UDF in Excel

Tutorial: How to loop through a range of cells in a UDF, User Defined Function, in Excel. This is ...

Tutorial: How to loop through a range of cells in a UDF, User Defined Function, in Excel. This is ...