How to use Sumproduct Function in Excel
In this Excel tutorial, you can learn how to use SUMPRODUCT function in Excel.
The SUMPRODUCT function calculates the sum of the products of corresponding components in the given arrays. The name SUMPRODUCT accurately describes its function: it multiplies corresponding elements in the provided arrays and then sums those products.
This function is frequently used for weighted averages, matrix calculations, and other calculations involving multiple sets of data.
Sumproduct function syntax
Syntax is:
=SUMPRODUCT(array1,[array2],[array3],…)
Sumproduct basics
=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
Notes:
The SUMPRODUCT function accepts between one and 255 arrays as arguments, where corresponding elements in each array are multiplied together. A crucial requirement is that all arrays must have the same dimensions; that is, the ranges of cells must have the same number of rows and columns. If the arrays are not of compatible dimensions, the SUMPRODUCT function returns the #VALUE! error.
How does the SUMPRODUCT function work?
The SUMPRODUCT function works by multiplying the corresponding cells in the two arrays and then summing the products. For example, if you have two arrays, A and B, and you want to calculate the sum of the products of the elements in the first row of A and the first column of B, you would use the following formula:
=SUMPRODUCT(A1:A2, B1:B2)
Examples of Sumproduct function
Here are some examples of how to use the SUMPRODUCT function in Excel:
Sum of sales in report
Suppose you have a sales report with quantities sold in column B (B3:B12) and prices per unit in column C (C3:C12).
To calculate the total sales revenue, you would use the following formula: =SUMPRODUCT(B3:B12,C3:C12).
This multiplies the quantity of each item by its price and then sums all the resulting products.
Calculating weighted average with Sumproduct function
Suppose you have exam scores in cells A3:A7 and their respective weights in cells B3:B7.
To calculate the weighted average, use the formula: =SUMPRODUCT(A3:A7,B3:B7)/SUM(B3:B7).
This multiplies each score by its weight, sums those products, and then divides by the sum of the weights.
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
from =SUMPRODUCT(A3:A7,B3:B7)/SUM(B3:B7)
to =SUMPRODUCT(POINTS,WAGES)/SUM(WAGES)
Leave a Reply