How to use the SUMPRODUCT Function in Google Sheets
Calculates the sum of the products of corresponding entries in two equal-sized arrays or ranges.
The SUMPRODUCT function in Google Sheets is a versatile and powerful tool for performing calculations on arrays of data. It multiplies corresponding components in the given arrays and returns the sum of those products. This function is particularly useful when you need to perform complex calculations that involve multiple ranges of data, such as weighted averages, conditional sums, or combining multiple criteria in a single calculation.
??Purchase our book to improve your Excel productivity
Benefits
1. Versatility: It can handle multiple arrays and perform a variety of calculations.
2. Efficiency: Eliminates the need for helper columns by performing calculations within a single formula.
3. Conditional Calculations: Can incorporate conditions directly into the formula, allowing for dynamic and flexible data analysis.
4. Weighted Averages: Simplifies the calculation of weighted averages by combining weights and values in one step.
Step-by-Step Guide
Syntax
SUMPRODUCT(array1, [array2, ...])
- array1: The first array or range of cells to multiply.
- array2, ...: Additional arrays or ranges of cells to multiply (optional).
Basic Example
1. Input Data: Assume you have the following data in your Google Sheet.
| A | B | C |
|---------|--------|---------|
| Product | Price | Quantity|
| Apple | 2 | 10 |
| Banana | 1 | 20 |
| Cherry | 3 | 15 |
2. Calculate Total Sales: To calculate the total sales (Price * Quantity) for all products, use the SUMPRODUCT function.
=SUMPRODUCT(B2:B4, C2:C4)
This will multiply each price by its corresponding quantity and sum the results:
- Apple: 2 * 10 = 20
- Banana: 1 * 20 = 20
- Cherry: 3 * 15 = 45
The result will be 20 + 20 + 45 = 85.
??Purchase our book to improve your Excel productivity
Example
Scenario
You manage a store and track sales data, including discounts and categories for different products. Here is your data:
| A | B | C | D | E |
|---------|--------|---------|----------|---------|
| Product | Price | Quantity| Discount | Category|
| Apple | 2 | 10 | 0.1 | Fruit |
| Banana | 1 | 20 | 0.05 | Fruit |
| Carrot | 1.5 | 30 | 0.2 | Vegetable|
| Dates | 3 | 25 | 0.15 | DryFruit|
| Eggplant| 2.5 | 10 | 0.1 | Vegetable|
Calculations
1. Calculate Total Revenue After Discount:
=SUMPRODUCT(B2:B6, C2:C6, 1-D2:D6)
This formula calculates the total revenue considering the discounts for each product:
- Apple: 2 10 (1 - 0.1) = 18
- Banana: 1 20 (1 - 0.05) = 19
- Carrot: 1.5 30 (1 - 0.2) = 36
领英推荐
- Dates: 3 25 (1 - 0.15) = 63.75
- Eggplant: 2.5 10 (1 - 0.1) = 22.5
The total revenue will be 18 + 19 + 36 + 63.75 + 22.5 = 159.25.
2. Calculate Revenue for a Specific Category (e.g., Vegetables):
=SUMPRODUCT((E2:E6="Vegetable")*(B2:B6)*(C2:C6)*(1-D2:D6))
This formula uses a condition to sum the revenue for only the 'Vegetable' category:
- Carrot: 1.5 30 (1 - 0.2) = 36
- Eggplant: 2.5 10 (1 - 0.1) = 22.5
The total revenue for Vegetables will be 36 + 22.5 = 58.5.
??Purchase our book to improve your Excel productivity
Advanced Tips
1. Handling Errors: Use IFERROR to handle any potential errors within your SUMPRODUCT formula.
=IFERROR(SUMPRODUCT((E2:E6="Vegetable")*(B2:B6)*(C2:C6)*(1-D2:D6)), "No Data")
2. Combining Multiple Conditions: Combine multiple conditions using multiplication.
=SUMPRODUCT((E2:E6="Vegetable")*(B2:B6 > 2)*(B2:B6)*(C2:C6)*(1-D2:D6))
This formula calculates the revenue for vegetables with a price greater than 2.
3. Using Named Ranges: Simplify your formulas by using named ranges.
=SUMPRODUCT((Category="Vegetable")*(Price)*(Quantity)*(1-Discount))
Define named ranges for each column for easier readability and maintenance.
4. Array Formulas: Use array formulas to create dynamic ranges.
=ARRAYFORMULA(SUMPRODUCT((E2:E6="Vegetable")*(B2:B6)*(C2:C6)*(1-D2:D6)))
Array formulas automatically adjust as data ranges change.
??Purchase our book to improve your Excel productivity :
??102 Most Useful Excel Functions with Examples: The Ultimate Guide
???? Order it here : https://lnkd.in/enmdA8hq
?? Transform from novice to pro with:
?? Step-by-Step Guides
??? Clear Screenshots
?? Real-World Examples
?? Downloadable Practice Workbooks
?? Advanced Tips
??Newsletters that might interest you :