Excel SUMPRODUCT Explained

SUMPRODUCT multiplies matching elements of two or more arrays and returns the sum of those products. That one behavior covers weighted averages, conditional counts, and conditional sums with OR logic and calculated conditions that SUMIFS cannot express.

How Do You Calculate a Weighted Average with SUMPRODUCT?

Multiply each value by its weight, sum the products, and divide by the total weight — SUMPRODUCT does the first two steps in one call.

Data (A1:B4):
Score   Weight
90      0.5
80      0.3
70      0.2

=SUMPRODUCT(A2:A4, B2:B4) / SUM(B2:B4)
' = (90*0.5 + 80*0.3 + 70*0.2) / 1
' Result: 83

How Does SUMPRODUCT Count and Sum with Conditions?

Comparisons like A2:A6="North" produce TRUE/FALSE arrays; once coerced to 1s and 0s, summing them counts matches, and multiplying by a values column sums only matching rows.

Data (A1:C6):
Region   Product   Sales
North    Widget    500
South    Widget    300
North    Gadget    700
South    Gadget    200
North    Widget    450

' Count rows where Region is North:
=SUMPRODUCT(--(A2:A6="North"))
' Result: 3

' Sum Sales where Region is North AND Product is Widget:
=SUMPRODUCT((A2:A6="North")*(B2:B6="Widget")*C2:C6)
' = 500 + 450 = 950

' Sum only the Sales values over 400:
=SUMPRODUCT(--(C2:C6>400), C2:C6)
' = 500 + 700 + 450 = 1650

' OR logic — Sales where Product is Gadget OR Sales >= 500:
=SUMPRODUCT(--((B2:B6="Gadget")+(C2:C6>=500)>0), C2:C6)
' = 500 + 700 + 200 = 1400

Why Does the Double Negative (--) Work?

SUMPRODUCT ignores TRUE/FALSE values (treats them as 0), so you must convert them to numbers first: the first minus turns TRUE into -1, the second flips it to 1. Any math operation works — *1 and +0 do the same thing — but -- is the conventional idiom.

=SUMPRODUCT(A2:A6="North")        ' 0  (booleans ignored)
=SUMPRODUCT(--(A2:A6="North"))    ' 3  (coerced to 1/0)

When you multiply two boolean arrays together, the * itself does the coercion, which is why (cond1)*(cond2) needs no --.

SUMPRODUCT vs SUMIFS: Which Should You Use?

Use SUMIFS and COUNTIFS for plain AND conditions — they are faster on large ranges and easier to read. Reach for SUMPRODUCT when you need OR logic, conditions on calculated values (like MONTH(dates)=3), or criteria across multiplied columns like price times quantity.

' SUMIFS can't test a calculation; SUMPRODUCT can:
=SUMPRODUCT((MONTH(D2:D100)=3)*C2:C100)   ' March sales, no helper column

' Revenue = price * qty, filtered, in one formula:
=SUMPRODUCT((A2:A6="North")*C2:C6*E2:E6)

Common Pitfalls

• Whole-column references: SUMPRODUCT(--(A:A="North")) processes over a million cells. Use bounded ranges.
• Mismatched array sizes: A2:A6 against B2:B7 returns #VALUE!.
• Text in a values column: multiplying text with * throws #VALUE!; the comma form SUMPRODUCT(array1, array2) treats text as 0 instead.
• Don't use it for single lookups: to return one matching value rather than a sum, use XLOOKUP with the same boolean-array trick.