The SUMPRODUCT function in Excel is a powerful tool that multiplies corresponding values from two or more arrays and then sums the products. It’s highly useful when you need to calculate weighted averages, conditional sums, and other advanced calculations.
Syntax
| Function | Description |
|---|---|
| SUMPRODUCT(array1, [array2], ...) | Multiplies corresponding values in the arrays and returns the sum of these products. |
Parameters
- array1: The first array of values you want to multiply.
- array2, ... (optional): Additional arrays of values to multiply. All arrays must have the same dimensions.
Example
Let’s look at a slightly complex example involving quantities and prices for different items.
| Item | Quantity | Price per Unit |
|---|---|---|
| Item A | 5 | 10 |
| Item B | 3 | 15 |
| Item C | 10 | 7 |
| Item D | 8 | 12 |
Task: Calculate the total revenue by multiplying the quantity of each item by its price per unit and summing the results.
To solve this:
=SUMPRODUCT(B2:B5, C2:C5)This formula multiplies each quantity in B2:B5 by its corresponding price in C2:C5 and then sums the products, giving you the total revenue, which is 299.
Solution for Example
To calculate the total revenue, use:
=SUMPRODUCT(B2:B5, C2:C5)This formula multiplies the quantities in B2:B5 by the prices in C2:C5 and sums the products. The total revenue is 299.
Practice Exercise 1
Task: Using the table below, calculate the total weighted score for a student by multiplying each subject score by the respective weight and summing the results.
Hint: Use the SUMPRODUCT function to multiply the scores by the weights and sum them.
Practice Exercise 2
Task: Using the table below, calculate the total cost for each project by multiplying the hours spent by the rate per hour and summing the results.
Hint: Use the SUMPRODUCT function to multiply the hours worked by the rate per hour for each project and calculate the total cost.
Solution for Practice Exercise 1
To calculate the total weighted score for the student:
=SUMPRODUCT(B2:B5, C2:C5)This formula multiplies the scores in B2:B5 by the weights in C2:C5 and sums the results. The total weighted score is 565.
Solution for Practice Exercise 2
To calculate the total cost for all projects:
=SUMPRODUCT(B2:B5, C2:C5)This formula multiplies the hours worked in B2:B5 by the rate per hour in C2:C5 and sums the products. The total cost for all projects is 8450.