The `SUMPRODUCT` function in Excel is a powerful tool used to multiply corresponding elements in arrays or ranges and then sum up those products. It is particularly useful for tasks like calculating weighted averages or multiplying and summing rows or columns of data based on specific criteria. Here’s a step-by-step guide on how to use `SUMPRODUCT`:
Syntax
SUMPRODUCT(array1, [array2], [array3], ...)
- array1: This is the first array or range that you want to multiply and sum. It is required.
- array2, array3, …: These are additional arrays or ranges which are optional. All arrays must have the same dimensions; otherwise, `SUMPRODUCT` will return an error.
Basic Example
Suppose you have two columns: Quantity and Price per Unit, and you want to calculate the total value of the inventory.
=SUMPRODUCT(A2:A5, B2:B5)
- Data Setup:
- Column A contains quantities (`A2:A5`).
- Column B contains price per unit (`B2:B5`).
- Formula:
This formula multiplies each element in the `A` range with the corresponding element in the `B` range, then sums up the results.
Weighted Average Example
If you want to calculate a weighted average, where Column A is the scores and Column B is the weight:
=SUMPRODUCT(A2:A5, B2:B5) / SUM(B2:B5)
- Data Setup:
- Column A contains scores (`A2:A5`).
- Column B contains weights (`B2:B5`).
- Formula:
This formula calculates the weighted sum of the scores and divides it by the total of the weights to get the weighted average.
Conditions with SUMPRODUCT
You can also use `SUMPRODUCT` in conditional calculations by including conditions directly in the formula:
=SUMPRODUCT((A2:A5>10) * A2:A5 * B2:B5)
- Suppose you want to sum the products where quantities are greater than 10:
Here, `(A2:A5>10)` creates an array of TRUE/FALSE values that are coerced to 1/0 by multiplying with `A2:A5` and `B2:B5`, effectively filtering the data.
Important Tips
- Ensure that all arrays have compatible dimensions; otherwise, you will get a `#VALUE!` error.
- `SUMPRODUCT` can handle logical values encoded as arrays, where TRUE is treated as 1 and FALSE as 0.
- If you have a recent version of Excel (Excel 365 or Excel 2019 onward), consider `SUMPRODUCT` alongside `FILTER`, `ARRAYFORMULA`, or `XLOOKUP` for complex scenarios.
By understanding these basics and examples, you’ll be able to efficiently utilize the `SUMPRODUCT` function for various data analysis tasks in Excel.
Unlock Your Potential
Excel
Access
Power BI
