Excel SUMPRODUCT Function
SUMPRODUCT multiplies matching array items and then adds the products.
Use it for weighted totals, quantity times price calculations, scoring models, and conditional math where each row contributes a calculated amount.
SUMPRODUCT syntax & arguments
Syntax
=SUMPRODUCT(array1, [array2], ...)
-
1
array1
RequiredThe first range or array to multiply and then add.
-
2
array2
Optional RepeatableAdditional ranges or arrays to multiply by position before the products are added.
Example
=SUMPRODUCT(B2:B10, C2:C10)
Multiply each value in B2:B10 by the matching value in C2:C10, then add the products.
SUMPRODUCT caveats
SUMPRODUCT is flexible because it can combine array math and aggregation, but array shape and coercion rules matter.
-
Arrays must be the same shape
Each array should have the same number of rows and columns so every value has a matching position.
-
Non-numeric entries act like zero
Text inside supplied arrays does not contribute numeric value, which can hide dirty source data.
-
Use TRUE/FALSE tests for conditional SUMPRODUCT
Excel does not have a native SUMPRODUCTIF function, so conditions are added as 1/0 test arrays. A test like
B2:B10="East"creates TRUE/FALSE results. Use--to convert TRUE to1and FALSE to0, then pass those test arrays alongside the values to sum, such as=SUMPRODUCT(--(B2:B10="East"), --(C2:C10="Complete"), D2:D10). Rows that fail a test multiply by zero and do not contribute to the total. -
Large ranges can be slow
SUMPRODUCT evaluates arrays directly, so full-column references can make workbooks recalculate slowly.
Using straightforward criteria? SUMIFS is usually clearer when you only need to add values that meet normal criteria pairs.
Intro SUMPRODUCT practice problem
Solve the intro problem directly here, or open it on its own page.
Advanced SUMPRODUCT practice problems
Use SUMPRODUCT alongside other Excel functions in realistic, less-prescriptive challenges.
