In this lesson you can learn how to use SUMPRODUCT function in Excel.
SUMPRODUCT is the most important (next to SUM) Excel function used in operations research. This function multiplies in the given arrays, and returns the sum of those products.
=SUMPRODUCT (A1:C1,A2:C2) means A1*A2+B1*B2+C1*C2
=SUMPRODUCT(A4:A7,B4:B7) means A4*B4+A5*B5+A6*B6+A7*B7
=SUMPRODUCT(A9:B11,D9:E11) means A9*D9+A10*D10+A11*D11+B9*E9+B10*E10+B11*E11
=SUMPRODUCT(E9:E11,G9:G11) means E9*G9+E10*G10+E11*G11
(array1, [array2], [array3], ...) is from 2 to 30 arrays whose elements you multiply and add. Arrays must be in the same dimensions (ranges of cells must have the same number of rows and columns). If it is not, the function SUMPRODUCT returns the #VALUE! error.
Example 1 Sum of sales in report
You have sales report. In B column there is count of pieces sold. In C column there are prices. How to calculate sum of sales?
Formula here is: =SUMPRODUCT(B3:B12,C3:C12)
Result is $223.08.
Example 2 Calculating weighted average with Sumproduct function
To calculate weighted average of exam's result in Excel use below formula:
As you see points in 5th row are the most important.
Example 3 SUMPRODUCT and name ranges
Let's use the same example. You can name ranges and use them instead of ranges. I named ranges A3:A7 as POINTS and B3:B7 as WAGES.
Now I can change formula