The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. This sounds boring, but SUMPRODUCT is an elegant and versatile function, which this example illustrates nicely.
In this example, SUMPRODUCT is configured with two arrays. The first array is the range that holds product pricing:
Note the reference is absolute to prevent changes as the formula is copied to the right. This range evaluates to the following array:
The second array is generated with this expression:
The result of D5:D9="x" is an array of TRUE FALSE values like this:
and returns the sum of products, 168 in this case.
Effectively, the second array acts as a filter for the values in the first array. Zeros in array2 cancel out items in array1, and 1s in array2 allow values from array1 to pass through into the final result.
With a single array
SUMPRODUCT is set up to accept multiple arrays, but you can simplify this formula a bit by providing a single array at the start:
SUMPRODUCT accepts the range B3:B6 as an array of four cells. For each cell in the array, LEN calculates the length of the text as a number. The result is an array that contains 4 numbers. SUMPRODUCT then sums the items in this array and returns the...
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...
Excel Formula Training
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.