The SUM function then calculates the total for the filtered array. In this case, it returns empty strings ( ') for all error values because they evaluate to TRUE, and it returns the remaining values from the range ( Data) because they evaluate to FALSE, meaning that they don't contain error values. For example, this array formula sums just the positive integers in a range named Sales, which represents cells E9:E24 in the example above: =SUM(IF(Sales0,Sales)) The IF function creates an array of positive and false values. You might need to sum values based on conditions. You can simplify the formula even more: =SUM(IF(ISERROR(Data).1)) This version works because TRUE.1=1 and FALSE.1=0. You can simplify the formula and achieve the same result by removing the third argument for the IF function, like this: =SUM(IF(ISERROR(Data),1)) If you don't specify the argument, the IF function returns FALSE if a cell does not contain an error value. Count the number of error values in a range This example is like the previous formula, but it returns the number of error values in a range named Data instead of filtering them out: =SUM(IF(ISERROR(Data),1,0)) This formula creates an array that contains the value 1 for the cells that contain errors and the value 0 for the cells that don't contain errors.
0 kommentar(er)
