Excel SUMPRODUCT: multiply and add in one step (with conditions)

SUMPRODUCT multiplies two (or more) columns row by row, then adds up the results. No helper column needed.

The basic formula

=SUMPRODUCT(A2:A10,B2:B10)

Prices in A, quantities in B. It does A2×B2 + A3×B3 + … and returns the total sales. With 10×3 and 25×2 that is 30 + 50 = 80.

All ranges must be the same size, or you get #VALUE!.

Weighted average

=SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10)

Scores in B, weights in C. Full walkthrough: weighted average.

Sum with conditions (the power move)

Total sales for North in March, from region (A), date (B) and amount (C):

=SUMPRODUCT((A2:A100="North")*(MONTH(B2:B100)=3)*C2:C100)

Each test in brackets gives TRUE/FALSE for every row. Multiplying turns TRUE into 1 and FALSE into 0, so only matching rows survive. SUMIFS cannot do MONTH() inside its criteria — SUMPRODUCT can.

Count with conditions

=SUMPRODUCT((A2:A100="North")*(C2:C100>1000))

OR conditions

North or South: add the tests instead of multiplying, and wrap in a test for “more than 0”:

=SUMPRODUCT(((A2:A100="North")+(A2:A100="South")>0)*C2:C100)

Tips

• Avoid whole columns (A:A) — SUMPRODUCT processes every row and slows down.
• For simple conditions, SUMIFS is faster and easier to read.