SUMPRODUCT
Multiplies corresponding components in given arrays and returns the sum of those products.
SUMPRODUCT(array1, [array2], ...)Arguments
array1First arrayarray2Additional arrays to multiply(optional)
=SUMPRODUCT(A1:A3,B1:B3)A1*B1 + A2*B2 + A3*B3
=SUMPRODUCT((A:A="Yes")*1)Counts matching values
=SUMPRODUCT(Qty,Price)Multiplies quantity by price
- •Arrays must be the same size
- •Powerful for conditional calculations
- •Can replace many SUMIF scenarios
- •Using ranges of different sizes in SUMPRODUCT's arrays, which causes a #VALUE! error since every array must have matching dimensions
- •Forgetting SUMPRODUCT treats text and logical values inconsistently across contexts, which can silently return 0 instead of the expected error or result
- •Overcomplicating a simple SUMIFS-style calculation with SUMPRODUCT when SUMIFS would be simpler and faster for a straightforward conditional sum
SUMIFSHandles simple multi-condition sums more simply than SUMPRODUCT, when the logic doesn't need array math.SUMAdds a straightforward range without the array-multiplication logic SUMPRODUCT provides.COUNTIFSCounts based on multiple conditions, a simpler alternative when you don't need SUMPRODUCT's weighted-sum capability.Why does SUMPRODUCT return a #VALUE! error?
The arrays you're multiplying together probably have different dimensions - every array argument needs to be the exact same size and shape.
Can SUMPRODUCT do what SUMIFS does?
Yes, and more - SUMPRODUCT can weight and combine conditions in ways SUMIFS can't, but for a simple multi-condition sum, SUMIFS is usually simpler and faster.
Why does SUMPRODUCT return 0 unexpectedly?
Check whether one of the arrays contains text where a number was expected - multiplying text by a number returns an error in some contexts and is treated as 0 in others, which can mask a real data problem.
Need to translate a formula using SUMPRODUCT?
Use our translator to convert your complete formula