Discover how to use SUMPRODUCT function in a formula.
When to use
SUMPRODUCT multiplies corresponding components in the given arrays, and returns the sum of those products
- Enables the user to avoid using complicated formulas by summing up the products of individual corresponding cells within the indicated arrays
- Array arguments to be calculated must be of the same dimension
- Multiplies the array arguments, which must have the appropriate dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.
- SUMPRODUCT treats array entries that are not numeric as if they were zeros.
- Very powerful function that can often be used instead of array formulae.
- To find the Jo’s sales for Business Unit A in the North region with tax, with in the above example, using the SUMPRODUCT function, the formula is as follows
- =SUMPRODUCT((B5:B16=J4)*(C5:C16=J5)*(D5:D16=J6)*(E5:E16=J7)*F5:F16) (which is $500)
- The first part of the formula ((B5:B16=J4) is saying: if the data in B5:B16 match what is in cell J4 (A) a TRUE result will appear. A TRUE result will give the value = 1 and a FALSE result will give the value = 0
- Each part of the formula is doing the same thing but using different cells to work out whether the value will be TRUE or FALSE
- Each sections of the formula are multiplied by each other, giving the result of 500
Lesson notes are only available for subscribers.