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.
SUMPRODUCT · In this guide
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
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Qty | Price | Region |
| 2 | Pens | 10 | 1.50 | North |
| 3 | Notebooks | 4 | 3.25 | South |
| 4 | Folders | 6 | 2.00 | North |
| 5 | Markers | 8 | 1.75 | East |
| 6 | Staplers | 2 | 7.50 | South |
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.