Everyone knows to use SUM to add a range. But if the range contains error values, SUM won’t work — what do you use then? If you want to exclude values in hidden rows, SUM also can’t do that — what then? And if the range contains both error values and hidden rows, SUM still fails — what can you use?
To sum while excluding error values, there are two methods:
-
=SUMIF(B2:B6,"<9.9E+99")
- 9.9E+99 is an astronomically large number
- In Excel, error values are treated as larger than any number
- So in B2:B6 any value smaller than that astronomical number (i.e., all numeric values) will be summed
-
=SUMPRODUCT(IFERROR(B2:B6,0))
- Convert any error values in B2:B6 to 0
- Then sum the original numbers and the zeros converted from error values — this effectively avoids the errors
To sum while excluding hidden rows, use the following method:
-
=SUBTOTAL(109,B2:B6)
- In SUBTOTAL, function numbers 9 and 109 perform SUM
- 109 additionally excludes manually hidden rows when summing
If the sum range contains both error values and hidden rows, use the following method:
-
=AGGREGATE(9,7,B2:B6)
- In AGGREGATE, function number 9 is SUM
- The second argument 7 tells AGGREGATE to ignore hidden rows and error values when performing the function specified by the first argument
