The SUMPRODUCT function accepts one or more arrays, multiplies the arrays together, and returns the "sum of products" as a final result. If only one array is supplied, SUMPRODUCT simply returns the sum of items in the array.
In the example shown, the goal is to count errors in a given range. The formula in E5 is:
Working from the inside out, the ISERROR function returns TRUE when a cell contains an error, and FALSE if not. Because there are five cells in the range B5:B9, ISERROR evaluates each cell and returns five results in an array like this:
To coerce the TRUE/FALSE values to 1's and 0's, we use a double negative (--). The resulting array looks like this:
Finally, SUMPRODUCT sums the items in this array and returns the total, which is 3 in this case.
The ISERROR function counts all errors. If for some reason you want to count all errors except #N/A, you can use the ISERR function instead:
In this example, the goal is to count the number of cells in a range that do not contain errors. Working from the inside out, we first use the ISERROR function on the entire range: ISERROR ( B5:B14 ) // check all 10 cells Since there are ten cells...
The AVERAGEIF function can calculate an average of numeric data with one or more criteria. In this case, the criteria is the expression ">=0". This filters out error values, and AVERAGEIF returns the average of the remaining eight values, 91.75...
The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. This sounds boring, but SUMPRODUCT is an incredibly versatile function that can be used to count and sum like COUNTIFS or SUMIFS, but with more...
The Excel ISERROR function returns TRUE for any error type excel generates, including #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! You can use ISERROR together with the IF function to test for errors and display a custom message, or...
The Excel ISERR function returns TRUE for any error type except the #N/A error. You can use the ISERR function together with the IF function to test for an error and display a custom message, or perform a different calculation if found.
The Excel SUM function returns the sum of values supplied. These values can be numbers, cell references, ranges, arrays, and constants, in any combination. SUM can handle up to 255 individual arguments.
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.