Home › Excel functions › SUMPRODUCT

SUMPRODUCT Function in Excel, with Examples

The SUMPRODUCT function multiplies ranges row by row and adds up the results. Quantity × price for a whole order in one cell is the classic use, but SUMPRODUCT can also sum and count with conditions, including OR logic that SUMIFS can't do.

Excel CSE · 3 min read
In this guide
  1. SUMPRODUCT syntax
  2. Basic example
  3. Sum with conditions
  4. Count with conditions
  5. OR conditions
  6. Weighted average
  7. Common mistakes
  8. FAQ

SUMPRODUCT syntax

=SUMPRODUCT(array1, [array2], [array3], …)

Each array must have the same size. Excel multiplies the first values of each array, then the second values, and so on, and returns the total.

Basic example: order total

ABCD
1ProductQtyPriceRegion
2Pens101.50North
3Notebooks43.25South
4Folders62.00North
5Markers81.75East
6Staplers27.50South
=SUMPRODUCT(B2:B6, C2:C6)

Excel calculates 10×1.50 + 4×3.25 + 6×2.00 + 8×1.75 + 2×7.50 = 15 + 13 + 12 + 14 + 15 = 69. Without SUMPRODUCT you'd need a helper column with =B2*C2 and then a SUM.

Sum with a condition

Put a comparison in brackets. It returns TRUE or FALSE for every row, and multiplying turns those into 1 and 0. Rows that don't match are multiplied by 0 and drop out.

=SUMPRODUCT((D2:D6="North") * B2:B6 * C2:C6)

Only Pens and Folders are in North: 15 + 12 = 27.

Add more conditions by multiplying more brackets: (D2:D6="South") * (B2:B6>3) means South and more than 3.

Count with conditions

Leave out the values and SUMPRODUCT counts the rows where all conditions are true:

=SUMPRODUCT((D2:D6="South") * (B2:B6>3))

Only Notebooks is in South with a quantity above 3, so the result is 1. COUNTIFS gives the same here, but SUMPRODUCT also accepts calculations inside the condition, such as (MONTH(A2:A100)=3) to count everything in March.

OR conditions

Multiplying means AND. Adding two conditions means OR:

=SUMPRODUCT(((D2:D6="North") + (D2:D6="East")) * B2:B6)

This adds the quantities for North or East: 10 + 6 + 8 = 24. Keep the extra brackets around the OR part, or the multiplication only applies to the second condition.

Weighted average

What's the average price per item, taking quantities into account? Divide SUMPRODUCT by the total quantity:

=SUMPRODUCT(B2:B6, C2:C6) / SUM(B2:B6)

69 / 30 = 2.30. A plain AVERAGE of the prices would give 3.20, because it treats the 2 staplers the same as the 10 pens.

Common SUMPRODUCT mistakes

  • #VALUE! The ranges have different sizes, such as B2:B6 and C2:C7.
  • Result is 0 with a condition. You used a comma between a condition and a range: SUMPRODUCT((D2:D6="North"), B2:B6). TRUE/FALSE values count as 0 unless you multiply them. Use * or put -- in front of the condition.
  • Slow workbook. Avoid whole columns like A:A: SUMPRODUCT then calculates over a million rows.

Frequently asked questions

What's the difference between SUMPRODUCT and SUMIFS?

SUMIFS is faster and easier for simple AND conditions. SUMPRODUCT is more flexible: it handles OR logic, calculations inside conditions and multiplying columns before adding.

Do I need Ctrl+Shift+Enter for SUMPRODUCT?

No. SUMPRODUCT works with arrays on its own, even in old versions of Excel.

Can SUMPRODUCT count unique values?

Yes: =SUMPRODUCT(1/COUNTIF(A2:A10, A2:A10)) counts the distinct values in a list without blanks. See how to count unique values.

What does the double minus (--) do?

It turns TRUE and FALSE into 1 and 0, so =SUMPRODUCT(--(A2:A10="Yes")) counts the cells that say Yes.