Sum a Range of Cells that Contains Errors

Every now and then you will need to calculate sum (or average, min, max, etc.) of a range of cells that contains errors. Normal functions like SUM or AVERAGE will not work in this case. That’s why I am going to show you two ways of how to sum a range of cells that contains errors.

We are going to try to sum column D which includes an error value to get a total number of students.

To sum a range of cells that contains errors using Aggregate Function:

Aggregate Function is multi-purpose function that can act as a number of functions and deal with a number of situations. Here is how we are going to use it for this situation:

Aggregate function has an option to ignore error values:

You can read more about Aggregate Function here.

To sum a range of cells that contains errors using with an array formula:

Array formulas are powerful tools but a bit confusing to many. If you master array formulas, you can perform powerful calculations with relative ease. Here is how to use an array formula for summing a range of cells that contains errors:

Due to nature of array formulas, this formula evaluates Iferror Function for each cell through D2:D13 range and creates an array in memory that has cell values if the value is not an error value and blank if value is an error. Then Sum Function sums the values in this new array.

You can download example workbook for Sum a range of cells that contains errors and check formulas if you like.

If you have any questions, please leave a comment below.