SUMPRODUCT function

The SUMPRODUCT function in Excel is a powerful and versatile function that multiplies corresponding values in given arrays or ranges and then returns the sum of those products. It is commonly used for complex calculations, such as weighted averages, conditional summations, and matrix multiplications.

Syntax

=SUMPRODUCT(array1, [array2], [array3], ...)

Parameters

1. array1 (required): The first array or range of numbers to multiply.
2. [array2, array3, …] (optional): Additional arrays or ranges to multiply. You can specify up to 255 arrays in a formula.

Key Points

1. Array Multiplication: The SUMPRODUCT function multiplies the corresponding elements in each array, and then sums those products. All arrays must have the same number of rows and columns (or be of equal size).
2. No Need for SUM: The SUMPRODUCT function automatically sums the products of the arrays, so you don’t need to use the SUM function separately.
3. Handling Conditions: You can use logical conditions within the SUMPRODUCT function to perform conditional calculations by converting logical expressions into 1s (TRUE) or 0s (FALSE).

Examples

1. Basic SUMPRODUCT (Multiplying two arrays and summing the results):

   =SUMPRODUCT(A1:A5, B1:B5)
   

   Result: Multiplies corresponding values in ranges A1:A5 and B1:B5, and sums the results.

   For example, if A1:A5 contains the values 1, 2, 3, 4, 5 and B1:B5 contains 10, 20, 30, 40, 50:

   • $1 \times 10 = 10$
   • $2 \times 20 = 40$
   • $3 \times 30 = 90$
   • $4 \times 40 = 160$
   • $5 \times 50 = 250$

   Sum: $10 + 40 + 90 + 160 + 250 = 550$

2. Weighted Sum (Calculating weighted average): Suppose you have values in range A1:A5 and corresponding weights in B1:B5:

   =SUMPRODUCT(A1:A5, B1:B5) / SUM(B1:B5)
   

   Result: This formula calculates the weighted average of the values in A1:A5, using the weights in B1:B5.

3. Conditional SUMPRODUCT (Summing values based on multiple criteria): Let’s say you want to sum the product of values in A1:A5 and B1:B5, but only when the corresponding values in C1:C5 meet a condition (e.g., greater than 50):

   =SUMPRODUCT((A1:A5)*(B1:B5)*(C1:C5 > 50))
   

   Result: The SUMPRODUCT function checks if each value in C1:C5 is greater than 50, and multiplies the corresponding values in A1:A5 and B1:B5 only if the condition is TRUE.

   For example, if the values in C1:C5 are 40, 60, 80, 30, and 70, the result would include only the products of A1:B1, A2:B2, A3:B3, A5:B5 (where C2, C3, and C5 are greater than 50).

4. Multiplying arrays with multiple conditions (AND logic): Suppose you want to sum products of A1:A5 and B1:B5, but only where C1:C5 equals “Apple” and D1:D5 is greater than 50:

   =SUMPRODUCT((C1:C5="Apple")*(D1:D5>50)*(A1:A5)*(B1:B5))
   

   Result: This will only multiply and sum values where C1:C5 equals “Apple” and the corresponding value in D1:D5 is greater than 50.

5. Sum of squares of values in an array: To find the sum of the squares of the numbers in the range A1:A5:

   =SUMPRODUCT(A1:A5, A1:A5)
   

   Result: This will calculate the sum of the squared values in the range A1:A5.

Notes

• Array Size: All arrays or ranges used in SUMPRODUCT must have the same number of rows and columns; otherwise, Excel will return an error.
• Logical Conditions: In SUMPRODUCT, logical conditions such as >, <, =, or <> can be used, but they must be wrapped in parentheses and evaluated as arrays of 1s (TRUE) and 0s (FALSE).
• Efficiency: SUMPRODUCT is powerful but can be computationally expensive for large datasets, especially when using complex conditions. It is generally more efficient than combining multiple SUM and IF functions for complex conditional summing.

Related Functions

• SUMIF / SUMIFS: These functions sum values based on one or more conditions, but they are less flexible than SUMPRODUCT, which can handle more complex conditions and calculations.
• AVERAGE: While SUMPRODUCT can be used for weighted averages, the AVERAGE function simply calculates the mean of a range.
• COUNTIF / COUNTIFS: These functions count the number of cells that meet one or more criteria, similar to how SUMPRODUCT can be used to sum based on conditions.

The SUMPRODUCT function is an essential tool for advanced Excel users, allowing for complex conditional summing, array calculations, and even matrix operations without needing to rely on helper columns or additional formulas.