Quick Navigation

SUMPRODUCT Function

Excel 2007+

Summary

The SUMPRODUCT function multiplies corresponding elements in one or more arrays and then sums the resulting products. This powerful function handles multi-criteria calculations without needing array formulas or helper columns.

Syntax

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

Parameters

Parameter	Type	Required	Description
array1	`Array/Range`	Yes	The first array argument whose components you want to multiply and then add
[array2], [array3], ...	`Array/Range`	No	Up to 254 additional arrays whose components are multiplied by corresponding elements in array1

Using the SUMPRODUCT Function

SUMPRODUCT excels at multi-condition calculations and array operations. Use it for weighted sums, conditional aggregation, or complex criteria matching where traditional functions fall short. Replace commas with operators (+, -, *, /) for advanced arithmetic between arrays.

Common SUMPRODUCT Examples

Basic Product Sum

=SUMPRODUCT(C2:C5, D2:D5)

Multiplies corresponding values in two ranges (unit cost × quantity) and returns total: $78.97

Multi-Criteria Sales Total

=SUMPRODUCT((B2:B9="Cherries")*(C2:C9="East")*D2:D9)

Sums sales for Cherries in East region only using Boolean multiplication

Sales Agent Net Total

=SUMPRODUCT((Table1[Sales]+Table1[Expenses])*(Table1[Agent]=B8))

Calculates net total (sales + expenses) for specific agent using table references

Item-Size Sales Total

=SUMPRODUCT((A2:A10="Item Y")*(B2:B10="Size M")*C2:C10)

Totals sales for specific item and size combination: 62 units

Frequently Asked Questions

SUMPRODUCT handles more complex conditions, Boolean logic, and array operations that SUMIFS cannot perform.

Yes, all arrays must have identical dimensions or SUMPRODUCT returns #VALUE! error.

Yes, non-numeric values are treated as zero automatically.

Common Errors and Solutions

#VALUE!

Cause: Arrays have different dimensions

Solution: Ensure all ranges have identical size and shape

Slow performance

Cause: Using full column references like A:A

Solution: Use specific ranges like A2:A100 instead

Incorrect results

Cause: Missing parentheses with arithmetic operators

Solution: Enclose arrays in parentheses: SUMPRODUCT((A1:A10+B1:B10), C1:C10)

Notes

Avoid full column references (A:A) for performance
Use double unary (--) to convert TRUE/FALSE to 1/0: --(A1:A10>10)
Works in all Excel versions since 2007
Boolean multiplication: TRUE*TRUE=1, others=0
Parentheses control operation order with mixed operators

Compatibility

Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365

Not available in:

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+