Excel SUMPRODUCT function

Summary

The Excel SUMPRODUCT function multiplies corresponding elements in arrays and returns the sum of those products. It’s a versatile function used for various operations, including conditional summing, array-based calculations, and complex criteria evaluation.
Syntax
				
					=SUMPRODUCT(array1, [array2], [array3], ...)
				
			
  • array1: The first array or range to multiply
  • [array2], [array3], …: [Optional] Additional arrays or ranges to multiply with the first array
Return value
The sum of the products of corresponding elements in the given arrays.

How to use

SUMPRODUCT is ideal for scenarios where you need to multiply items across arrays or ranges and sum the results. It’s widely used in data analysis, financial modeling, and complex calculations involving multiple arrays.

Examples

Simple SUMPRODUCT
Multiplying and Summing Corresponding Values in Arrays: Multiplying two arrays and summing the products:
				
					=SUMPRODUCT(A2:A10, B2:B10)
				
			
This formula multiplies each corresponding element in the ranges A2:A10 and B2:B10 and sums the resulting products.
SUMPRODUCT for Conditional Summing
Summing with Conditions Without Using Criteria Ranges: To sum values in C2:C10 where corresponding values in D2:D10 are greater than 50:
				
					=SUMPRODUCT(C2:C10, --(D2:D10 > 50))
				
			
This formula sums the values in C2:C10 only for the cells where the corresponding cells in D2:D10 are greater than 50.
SUMPRODUCT with Multiple Arrays
Multiplying Multiple Arrays and Summing the Results: Combining more than two arrays:
				
					=SUMPRODUCT(E2:E10, F2:F10, G2:G10)
				
			
This formula multiplies each corresponding element in three arrays (E2:E10, F2:F10, G2:G10) and sums the resulting products.
SUMPRODUCT for Weighted Averages
Calculating Weighted Averages: To compute the weighted average:
				
					=SUMPRODUCT(H2:H10, I2:I10) / SUM(I2:I10)
				
			
Calculates the weighted average of values in H2:H10, where I2:I10 contains the weights.
SUMPRODUCT for Advanced Data Analysis
Complex Data Analysis with Multiple Conditions: Analyzing data with multiple conditions:
				
					=SUMPRODUCT((J2:J10="Red") * (K2:K10="Large") * L2:L10)
				
			
This formula sums the values in L2:L10 only for rows where J2:J10 is “Red” and K2:K10 is “Large”.

Additional Notes

  • SUMPRODUCT can handle up to 255 array arguments.
  • It can be used for array-based logic without requiring Control-Shift-Enter (CSE) for array formulas in older Excel versions.
  • Logical operations within SUMPRODUCT are often combined with double unary operators.

Related Functions

Excel SUMIF function

The Excel SUMIF function sums numbers based on a single condition, perfect for conditional totals in data analysis.

Excel COUNTIF function

The Excel COUNTIF function counts cells that meet a single condition, vital for targeted data quantification.

Excel COUNTIFS function

The Excel COUNTIFS function counts cells meeting multiple criteria, essential for complex data categorization.

Excel SUMIFS function

The Excel SUMIFS function sums numbers based on multiple criteria, enhancing detailed and conditional data aggregation.

Content Navigation