SUMPRODUCT

How SUMPRODUCT works

SUMPRODUCT walks every range you pass it in lockstep, multiplies the matching elements at each position, and adds the products up. With two ranges of qty and unit_price, you get total revenue with one formula instead of a helper column and a SUM. Same for any element-wise math: hours × rate for payroll, scores × weights for grading, allocations × returns for portfolio analysis.

The second life of SUMPRODUCT is conditional aggregation. Excel coerces TRUE to 1 and FALSE to 0 inside SUMPRODUCT, so an expression like (B2:B11="East") becomes an array of 0s and 1s. Multiply that by your value column and sum the result: you get only the rows where the condition is true. Add more parenthesised conditions to AND them together. This is how every senior analyst handled multi-criteria sums before SUMIFS arrived in 2007.

Modern Excel has SUMIFS, COUNTIFS, and dynamic arrays, so most criteria work has a cleaner alternative now. SUMPRODUCT still wins when you need a criterion involving an array operation ("sum the top 3 values", "multiply two filtered lists") or when you're stuck on Excel 2003 / Google Sheets / a shared workbook where SUMIFS doesn't behave. Knowing the boolean-coercion trick is a hallmark of someone who actually understands Excel.

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

Arguments

SUMPRODUCT basic: total revenue from qty × price

The classic SUMPRODUCT case. Six product lines with quantity sold in column B and unit price in column C. You want Q3 revenue across all products in one cell, without adding a helper column for line-item revenue first.

SUMPRODUCT multiplies B2*C2, B3*C3, ..., B7*C7 and adds the six products together. One formula, one number, no helper column.

=SUMPRODUCT(B2:B7, C2:C7)

• •B2:B7 -> the quantity column (first array, in blue)
• •C2:C7 -> the unit price column (second array, in purple)
• •Element-wise multiply -> B2*C2 + B3*C3 + ... + B7*C7
• •Result -> $128,550 - the sum of all six line-item revenues
• •Faster than a helper column -> no need to fill D with =B*C then SUM(D) - SUMPRODUCT does both in one step

Weighted vendor evaluation: scores × weights

Procurement is comparing five vendors on five criteria. Each criterion has a weight (decimals summing to 1.00) and each vendor has a 1-5 score per criterion. You want one weighted total per vendor so they're ranked on a single number.

For one vendor, SUMPRODUCT multiplies their score row by the weights row and sums the products. That's the weighted total. To rank multiple vendors, write the same formula per row using absolute refs on the weights so it doesn't shift on drag-down. The weights live in row 1 here, scores in row 2.

=SUMPRODUCT(B$1:F$1, B2:F2)

• •B$1:F$1 -> the weights row (row-anchored with $ so it stays fixed)
• •B2:F2 -> the vendor's score row (changes per vendor)
• •Element-wise multiply -> 0.30*4 + 0.25*5 + 0.20*3 + 0.15*4 + 0.10*3
• •Result -> 3.95 - the weighted score for Acme Corp on a 1-5 scale
• •Why weights matter -> Acme has high Price but low Delivery. The 30% Price weight rewards that exactly the right amount

SUMPRODUCT as SUMIFS: East + Q3 revenue total

Before SUMIFS shipped in 2007, this was the only way to sum a column under multiple conditions. It still works, and it handles array expressions SUMIFS can't (top N filtering, cross-array math).

The trick is boolean coercion. (B2:B11="East") produces an array of TRUEs and FALSEs. Multiplying it by another boolean array AND-combines the conditions. Multiplying by your value column zeroes out the non-matches and keeps the matches. SUMPRODUCT adds the survivors.

=SUMPRODUCT((B2:B11="East")*(C2:C11="Q3")*D2:D11)

• •(B2:B11="East") -> region check - returns TRUE/FALSE per row (purple)
• •(C2:C11="Q3") -> quarter check - same shape, ANDs with the region check (orange)
• •D2:D11 -> the values to actually sum (blue)
• •* multiplies the booleans -> (TRUE * TRUE * 5200) = 5200 - kept. (TRUE * FALSE * 4100) = 0 - dropped
• •Result -> $8,950 - the two East + Q3 rows summed
• •Modern alternative -> =SUMIFS(D2:D11, B2:B11, "East", C2:C11, "Q3") does the same thing in a cleaner shape

SUMPRODUCT as COUNTIFS: how many big East orders?

Same boolean coercion, no value column. SUMPRODUCT counts the rows where both conditions are true by adding up the 1s from (TRUE*TRUE) and ignoring the 0s.

How many orders were placed in the East region AND above $4,000? Two AND conditions, no amount multiplication - just count the matching booleans.

=SUMPRODUCT((B2:B11="East")*(D2:D11>4000))

• •(B2:B11="East") -> region check (purple)
• •(D2:D11>4000) -> amount threshold (blue)
• •No value column -> without a third array, the products are 1 (both true) or 0 (either false)
• •Sum of 1s = count -> this is exactly what COUNTIFS does, just written manually
• •Result -> 2 orders - Sarah ($5,200) and Ravi ($4,200) clear both conditions
• •Modern alternative -> =COUNTIFS(B2:B11, "East", D2:D11, ">4000") is cleaner; SUMPRODUCT still works

Where people go wrong

1. 01
   Mismatched array sizes return #VALUE!

   SUMPRODUCT requires every range to have the exact same dimensions. Passing B2:B11 with C2:C12 returns #VALUE! - it cannot pair the elements. Easy to do by accident when one column has a header offset and the other doesn't.

   Fix: Double-check the ending row of every range. Use the Name Box to highlight each range visually before committing the formula. If the data grows, switch to whole-column references (B:B, C:C) or a dynamic OFFSET.
2. 02
   Forgetting parentheses around boolean conditions

   Writing =SUMPRODUCT(B2:B11="East" * D2:D11) gives #VALUE!. Excel resolves * first because of operator precedence, multiplying text by numbers. The boolean has to be its own parenthesised expression so it coerces to 1/0 before the multiplication.

   Fix: Always parenthesise every condition: =SUMPRODUCT((B2:B11="East") * (D2:D11>4000) * E2:E11). Treat each condition as a unit and the formula reads predictably.
3. 03
   Mixing * and , as separators in conditions

   =SUMPRODUCT((B2:B11="East"), D2:D11) does NOT filter by region - it just sums two ranges separately. The comma keeps the boolean array as a separate argument, which sums to a count of TRUEs but doesn't AND with D2:D11.

   Fix: For conditional aggregation, use * inside SUMPRODUCT to combine the arrays positionally. Reserve commas for genuine multi-array dot products where every array contributes to every product.
4. 04
   Using whole-column refs without thought

   =SUMPRODUCT(B:B, C:C) works but Excel evaluates over a million rows. Each * happens for every empty cell pair. On a sheet with several SUMPRODUCT formulas, this turns into a noticeable lag.

   Fix: Use a structured reference (an Excel Table - B2:B11 becomes Table[Qty] and auto-expands as you add rows). It stays bounded to the data and the formula stays fast.

Notes

• Available in every Excel version. Pre-dates SUMIFS / COUNTIFS by about 15 years.
• All ranges must have the same dimensions. Mismatch returns #VALUE!.
• Booleans coerce to 1/0 inside SUMPRODUCT, which is the foundation of every multi-criteria SUMPRODUCT pattern.
• Without parentheses around each condition, operator precedence breaks the formula. Always parenthesise.
• Use * (multiplication) to AND conditions. Use + to OR conditions, but be careful — overlapping matches get counted twice unless wrapped in --SIGN().
• Modern Excel: prefer SUMIFS / COUNTIFS / AVERAGEIFS for simple multi-criteria work. Keep SUMPRODUCT for array-aware logic those functions can't express.
• Three or more arrays multiply positionally: SUMPRODUCT(A, B, C) = sum of A[i]*B[i]*C[i]. Useful for cost = qty × price × discount_multiplier per row.
• Empty cells multiply as 0. Text in a numeric array also coerces to 0 (sometimes hides bugs — check your data).

What to learn next

Now prove it