Excel: Putting advanced array formulas to work
Wednesday, January 2nd, 2008Sum a range that contains error values
The SUM function in Microsoft Excel doesn’t work when you try to sum a range that contains an error value such as #N/A. This example shows you how to sum the values in a range named “Data” that contains errors:
=SUM(IF(ISERROR(Data),"",Data))
The formula creates a new array that contains the original values minus any error values. Starting from the inner functions and working outward, the ISERROR function searches the cell range (Data) for errors. The IF function returns one value if a condition you specify evaluates to TRUE, and another value if it evaluates to FALSE. In this case, it returns empty strings (“”) for all error values because they evaluate to TRUE, and the remaining values from the range (Data again) because they evaluate to FALSE, meaning that they don’t contain error values. The SUM function then calculates the total for the filtered array.
Popularity: 17% [?]
