Different ways in getting sum of a range with “Error”
Thanks to the comments made by a friend, I would like to share a few workarounds to ignore errors with SUM.
1) Use a helper column
This would be an easy way if you have flexibility in adding helper column.
The formula in the helper column =IF(ISNUMER(B2),B2,0) is used to convert “error” into 0, and keep the original value if it is a number. Then SUM gets the total as usual.
2) SUMIF
The syntax
SUMIF(range,criteria,sum_range) ‘when sum_range is omitted, the cells in range are both evaluated by criteria and added if they match criteria.
The criteria set for SUMIF “<=9.99E+307” is an extremely large number that we could input in Excel. In this sense, only numbers in the range will be added.
3) SUM(IF) – using array formula
You have to input this formula by CTRL SHIFT ENTER, not just ENTER. You will see the {} in the formula bar if you have entered the formula successfully.
Basically, IFERROR(B2:B6,0) means “If the cell in the range is an error, then convert it to 0, otherwise keep the original value.”
It will give you the following array:
{1;0;3;4;5}
By putting SUM before that:
=sum({1;0;3;4;5})
As it is an array formula, you have to input the formula by CTRL SHIFT ENTER to tell Excel that you are going to input it as array formula in order to get the correct result, which is 13.
For Excel 2003 or earlier, use:
- =SUM(IF(ISNUMBER(B2:B6),B2:B6,0)) ‘CTRL SHIFT ENTER
- =SUM(IF(ISERROR(B2:B6),0,B2:B6),0) ‘CTRL SHIFT ENTER
4) AGGREGATE (Excel 2010 or later)
AGGREGATE is one of my favorite formulas in Excel 2010. It is an enhanced version of SUBTOTAL that give you more options.
The syntax
AGGREGATE(function_num, options, array,k)
- Function_num 9 –> SUM
- Options 6 –> Ignore errors
- Range B2:B6 –> the range of data to be added
It simply means “Pls give me the SUM of the data in the range B2:B6. Ignore errors pls!”
This function alone worths a separate post. For the moment if you want to know more about Aggregate (in Excel 2010), you may check it out from Excel help. 🙂
wmfexcel 