The SUMPRODUCT function returns the sum of products of corresponding values in arrays or ranges.
This can be useful for data analysis when you need to take into account values from different ranges.
Syntax
SUMPRODUCT(array1, [array2, ...])
Argument |
Description |
Permitted values |
|---|---|---|
array1 |
The first array or range. Its first value is multiplied with the first values in additional arrays or ranges, the second one with all second values, and so forth |
Any numerical ranges or arrays |
[array2, ...] |
(optional) Additional arrays or ranges. |
Similar to the first argument |
Example of use
Suppose you need to calculate the total cost of goods with different discounts. The data is in three columns:
–A1:A5 (Quantity): 5, 3, 10, 2, 7;
–B1:B5 (Price): 100, 150, 80, 200, 90;
–C1:C5 (Discount, coefficient): 0.9, 1, 0.8, 1, 0.95 (where 0.9 = 10% discount),
Then the formula =SUMPRODUCT(A1:A5, B1:B5, C1:C5) will multiply the corresponding rows in the three columns and display the total sum.
Result: (A1×B1×C1) + (A2×B2×C2) + (A3×B3×C3) + (A4×B4×C4) + (A5×B5×C5)=
(5×100×0.9) + (3×150×1) + (10×80×0.8) + (2×200×1) + (7×90×0.95)
= 450 + 450 + 640 + 400 + 598.5 = 2538.5
Notes
–All arrays must have the same size, otherwise the function will return the #VALUE! error.
–If the arrays contain text values or empty cells, they will be ignored in the calculation.