Table of Contents
What is SUMPRODUCT in Excel?
SUMPRODUCT function is an effective and flexible tool that’s mainly utilised for summing the products of comparable ranges or arrays. This function takes the relevant elements from the supplied arrays, multiplies them, and then adds the results together.
Syntax :
=SUMPRODUCT(array1, [array2], [array3], …)
- Array1 (required argument) – The first array argument, the elements of which you wish to multiply then add.
- Array2, Array 3 (optional argument) – This is the second (or third) array or range that we wish to multiply and then add.
Example 1: Simple Multiplication and Sum
Assume you have a list of products, unit prices, and quantities sold. You wish to calculate the overall sales revenue.
Data:
- Product Names: A, B, C (in column A)
- Unit Prices: 10, 20, 30 (in column B)
- Quantities Sold: 5, 3, 2 (in column C)
| A | B | C |
| Product | Unit Price | Quantity Sold |
| A | 10 | 5 |
| B | 20 | 3 |
| C | 30 | 2 |
Using SUMPRODUCT:
To calculate total sales revenue, use the SUMPRODUCT function to multiply each unit price by the quantity sold, then add the results.
Formula:
=SUMPRODUCT(B2:B4, C2:C4)
Explanation:
- B2:B4 refers to the range of unit prices.
- C2:C4 refers to the range of quantities sold.
- The function multiplies each pair of corresponding elements from these ranges:
- 10 * 5 = 50
- 20 * 3 = 60
- 30 * 2 = 60
- It then sums these products: 50 + 60 + 60 = 170
Result:
The formula =SUMPRODUCT(B2:B4, C2:C4) will return 170, which is the total sales revenue.
Example 2: Conditional SUMPRODUCT
If you only want to calculate the total sales for products that meet a specified requirement (e.g., products in a specific category)
Syntax:
=SUMPRODUCT((C2:C5 = “SpecificCategory”) * A2:A5 * B2:B5)
Example:
Assume you have a database with several columns and want to determine total revenue by multiplying Unit Price and Quantity Sold just for Specific products.
Calculate the total revenue for “Electronics” category products using Unit Price and Quantity Sold.
Data:
| A | B | C | D |
| Product | Category | Unit Price | Quantity |
| A | Electronics | 10 | 5 |
| B | Groceries | 20 | 3 |
| C | Electronics | 30 | 2 |
| D | Groceries | 25 | 4 |
| E | Electronics | 40 | 1 |
- Filter Data by Category:
To filter by category, we’ll use the SUMPRODUCT and IF functions. - Formula:
Enter the following formula in a cell to calculate the total revenue for “Electronics”:
=SUMPRODUCT((B2:B6=”Electronics”)C2:C6D2:D6) - Explanation:
- (B2:B6=”Electronics”): This part generates an array of TRUE/FALSE values when the condition is met. Returns: {TRUE, FALSE, TRUE, FALSE, TRUE}.
- C2:C6: This is the Unit Price range.
- D2:D6: This is the Quantity Sold range.
- The SUMPRODUCT function multiplies only rows in the “Electronics” category, treating TRUE as 1 and FALSE as 0.
- For Product A: 10 * 5 = 50 (included)
- For Product C: 30 * 2 = 60 (included)
- For Product E: 40 * 1 = 40 (included)
- For Products B and D: Ignored because they are not “Electronics”.
- Sum of included products: 50 + 60 + 40 = 150
4. Result:
The formula =SUMPRODUCT((B2:B6=”Electronics”)C2:C6D2:D6) will return 150, which is the total revenue for “Electronics” products.
Using Excel to Filter Data:
To do this in Excel:
Enter Data:
Place the data in the respective columns as shown above.
Enter Formula:
- Click on a blank cell where you want the result to appear.
- Enter the formula: =SUMPRODUCT((B2:B6=”Electronics”)C2:C6D2:D6).
Press Enter:
Excel will calculate the total revenue for the “Electronics” category using the Unit Price and Quantity Sold.
Example 3: Weighted Average
To calculate a weighted average where weights are in column A and values in column B:
=SUMPRODUCT(A2:A5, B2:B5) / SUM(A2:A5)
- All arrays must have the same dimensions; otherwise, SUMPRODUCT will return an error.
- It can handle multiple arrays, but all arrays must be of equal length.
Example:
Calculate the weighted average price of the products based on the number of units sold.
Data:
| A | B | C |
| Product | Price | Units Sold |
| P1 | 50 | 10 |
| P2 | 75 | 150 |
| P3 | 60 | 200 |
| P4 | 90 | 50 |
- Enter Data: Enter the data in an Excel sheet as shown above, starting from cell A1.
- Formula: To calculate the weighted average price, use the following formula:=SUMPRODUCT(B2:B5, C2:C5) / SUM(C2:C5)
- Explanation:
- B2:B5 indicates the pricing range.
- C2:C5 indicates the range of units sold.
- SUMPRODUCT(B2:B5, C2:C5) multiplies each price by its corresponding units sold and then sums those products:
- 50 ∗ 100 = 5000
- 75 ∗ 150 = 11250
- 60 ∗ 200 = 12000
- 90 ∗ 50 = 4500
- Total = 5000 + 11250 + 12000 + 4500 = 32750
- SUM(C2:C5) sums the units sold:
100 + 150 + 200 + 50 = 500 - To calculate the weighted average price, divide the sum of items by the total number of units sold.
32750 / 500 = 65.5
Result:
The formula =SUMPRODUCT(B2:B5, C2:C5) / SUM(C2:C5) will return 65.5, which is the weighted average price of the products.
Steps in Excel:
- Enter Data: Enter your data into an Excel spreadsheet exactly as shown in the table.
- Enter Formula:
Click on a blank cell where you want the result to appear.
Enter the formula: =SUMPRODUCT(B2:B5, C2:C5) / SUM(C2:C5). - Press Enter:
Excel will calculate the weighted average price of the products and display the result in the selected cell.
Let’s consider a complex example :
Advanced Sales Analysis with Multiple Criteria
Dataset:
You have sales information for many products, including unit price, quantity sold, region, and sales quarter. You wish to look at the total revenue, weighted average pricing, and total revenue for a certain region and quarter.
Data:
| A | B | C | D | E |
| Product | Unit Price | Quantity | Region | Quarter |
| P1 | 50 | 100 | North | Q1 |
| P2 | 75 | 150 | South | Q1 |
| P3 | 60 | 200 | East | Q2 |
| P4 | 90 | 50 | West | Q2 |
| P5 | 80 | 300 | North | Q1 |
| P6 | 70 | 180 | South | Q3 |
| P7 | 65 | 220 | East | Q4 |
| P8 | 85 | 90 | West | Q3 |
| P9 | 95 | 110 | North | Q4 |
| P10 | 55 | 170 | South | Q4 |
- Calculate the total revenue.
- Calculate the weighted average unit price.
- Calculate the total revenue for the “North” region in “Q1”.
- Calculate the weighted average unit price for the “South” region in “Q4”.
Steps and Formulas
1. Calculate Total Revenue:
- Formula: =SUMPRODUCT(B2:B11, C2:C11)
- Explanation: This multiplies each unit price by the corresponding quantity sold and then sums the results.
2. Calculate Weighted Average Unit Price:
- Formula: =SUMPRODUCT(B2:B11, C2:C11) / SUM(C2:C11)
- Explanation: This divides the total revenue by the total quantity sold to get the weighted average unit price.
3. Calculate Total Revenue for the “North” Region in “Q1”:
- Formula: =SUMPRODUCT((D2:D11=”North”)(E2:E11=”Q1″)B2:B11*C2:C11)
- Explanation: This uses logical tests to include only the rows where the region is “North” and the quarter is “Q1”, and then multiplies the unit price by the quantity sold and sums the results.
4. Calculate Weighted Average Unit Price for the “South” Region in “Q4”:
- Formula:
=SUMPRODUCT((D2:D11=”South”)(E2:E11=”Q4″)B2:B11C2:C11) / SUMPRODUCT((D2:D11=”South”)(E2:E11=”Q4″)*C2:C11) - Explanation: This uses logical tests to include only the rows where the region is “South” and the quarter is “Q4”, then calculates the total revenue and divides it by the total quantity sold for these criteria.
Complete Example in Excel:
Data Entry:
Enter the data into the cells as shown in the table above.
Formulas and Results:
1. Total Revenue:
- Formula in Cell F2: =SUMPRODUCT(B2:B11, C2:C11)
- The formula calculates the total revenue.
2. Weighted Average Unit Price:
- Formula in Cell F3: =SUMPRODUCT(B2:B11, C2:C11) / SUM(C2:C11)
- The formula calculates the weighted average unit price.
3. Total Revenue for the “North” Region in “Q1”:
- Formula in Cell F4: =SUMPRODUCT((D2:D11=”North”)(E2:E11=”Q1″)B2:B11*C2:C11)
- The formula calculates the total revenue for the “North” region in “Q1”.
4. Weighted Average Unit Price for the “South” Region in “Q4”:
- Formula in Cell F5:
=SUMPRODUCT((D2:D11=”South”)(E2:E11=”Q4″)B2:B11C2:C11) / SUMPRODUCT((D2:D11=”South”)(E2:E11=”Q4″)*C2:C11) - The formula calculates the weighted average unit price for the “South” region in “Q4”.
Key Points to Remember
- SUMPRODUCT returns an error if all arrays have different dimensions.
- It can handle multiple arrays, but all arrays must be of equal length.
Test yourself with this example :
You have inventory data for various products, including their unit cost, quantity in stock, category, and storage location.
Data:
| A | B | C | D | E |
| Product | Unit Cost | Quantity | Category | Location |
| P1 | 10 | 50 | Electronics | Warehouse 1 |
| P2 | 15 | 100 | Furniture | Warehouse 2 |
| P3 | 30 | 120 | Electronics | Warehouse 3 |
| P4 | 35 | 180 | Furniture | Warehouse 1 |
| P5 | 41 | 60 | Electronics | Warehouse 2 |
| P6 | 47 | 40 | Furniture | Warehouse 3 |
| P7 | 54 | 70 | Electronics | Warehouse 1 |
| P8 | 59 | 60 | Furniture | Warehouse 2 |
| P9 | 62 | 130 | Electronics | Warehouse 3 |
| P10 | 73 | 150 | Furniture | Warehouse 1 |
- Calculate the total inventory value.
- Calculate the weighted average cost per item.
- Calculate the total inventory value for the “Electronics” category in “Warehouse 1”.
- Calculate the total inventory value for the “Furniture” category in “Warehouse 2”.
- Calculate the weighted average cost per item for the “Electronics” category in “Warehouse 3”.
- Calculate the weighted average cost per item for the “Furniture” category in “Warehouse 3”.
Conclusion:
To sum up, SUMPRODUCT is a superb example of effectiveness and style among Excel functions. Through the utilisation of its potential, you may exceed the limitations of conventional spreadsheet calculations and set off on a path to increased efficiency and understanding. So go ahead and use SUMPRODUCT to its full potential to transform your Excel experience right now!