=SUMPRODUCT(B2:B6,C2:C6) multiplies each quantity in B by the price next to it in C, then adds up the results. It gives the order total in one cell, with no column of line totals.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Qty | Price | Line total | Total | |
| 2 | Pen | 4 | $1.50 | $6.00 | $30.70 | |
| 3 | Notebook | 2 | $3.25 | $6.50 | $30.70 | |
| 4 | Folder | 5 | $0.80 | $4.00 | ||
| 5 | Stapler | 1 | $7.90 | $7.90 | ||
| 6 | Marker | 3 | $2.10 | $6.30 |
F2 and F3 show the same $30.70. The line totals in column D are only there to show what SUMPRODUCT does: 4 × 1.50, 2 × 3.25, and so on, then a SUM. Change a quantity and both totals follow.
SUMPRODUCT syntax
=SUMPRODUCT(array1, [array2], [array3], ...)
- Each array is a range or a calculation that produces one, and all must have the same size, or SUMPRODUCT returns
#VALUE!. - With two or more arrays, the values in the same position are multiplied, then the products are added.
- With one array it just adds it up, which is what makes the condition forms below work:
=SUMPRODUCT((A2:A7="North")*C2:C7)has one array, already multiplied. - Text passed as its own argument counts as 0. Text inside a
*calculation causes#VALUE!.
SUMPRODUCT works with arrays in every Excel version without Ctrl+Shift+Enter (Cmd+Shift+Enter on a Mac), which is why it was the standard tool for conditional sums before SUMIFS existed, and still is for the cases SUMIFS cannot handle.
SUMPRODUCT with conditions
A comparison on a range, A2:A7="North", returns one TRUE or FALSE per row. Multiplying by it keeps the rows where it is TRUE (×1) and zeroes the others (×0). Multiply two comparisons for AND.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Formula | Result | |
| 2 | North | Apple | 120 | North sales | 230 | |
| 3 | South | Pear | 45 | North Apple sales | 150 | |
| 4 | North | Pear | 80 | Count North | 3 | |
| 5 | East | Apple | 55 | Count over 50 | 4 | |
| 6 | South | Apple | 200 | Without -- | 0 | |
| 7 | North | Apple | 30 |
F2 adds the three North rows: 230. F3 multiplies two conditions, so a row counts only when both are true: 150. To count rather than sum, leave out the values and turn the TRUE/FALSE into numbers with -- (two minus signs): F4 counts 3 North rows. F6 shows why the -- matters: SUMPRODUCT does not add TRUE values, so the formula without it returns 0.
The first four give the same results as SUMIF, SUMIFS and COUNTIF. The next section is where SUMPRODUCT earns its place.
Conditions SUMIFS cannot express
SUMIFS compares a column with a fixed criterion. It cannot take the month of a date, compare two columns with each other, or multiply quantity by price before adding. SUMPRODUCT can, because each condition is an ordinary calculation.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Date | Target | Actual | Formula | Result | |
| 2 | North | 2026-01-05 | 100 | 120 | February sales | 135 | |
| 3 | South | 2026-01-12 | 60 | 45 | Rows over target | 3 | |
| 4 | North | 2026-02-03 | 90 | 80 | North or East sales | 285 | |
| 5 | East | 2026-02-18 | 50 | 55 | Above target by | 75 | |
| 6 | South | 2026-03-02 | 150 | 200 | |||
| 7 | North | 2026-03-20 | 40 | 30 |
- G2 takes the MONTH of every date and keeps the February rows: 80 + 55 = 135. This adds February of any year; add
*(YEAR(B2:B7)=2026)for one year only. - G3 compares two columns row by row and counts the rows where Actual beats Target.
- G4 is an OR: adding two conditions gives 1 when either is true (2 when both are, which is why the
>0is there). North or East: 285. - G5 adds how much each row beat its target by, only for the rows that did.
SUMPRODUCT for weighted totals and averages
Quantity times price is a weighted total, and conditions can be added to it. The same idea divided by the sum of the weights gives a weighted average: =SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6) is the average price per item sold.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Qty | Price | Formula | Result | |
| 2 | North | Apple | 10 | $1.20 | North revenue | $31.00 | |
| 3 | South | Pear | 4 | $1.50 | All revenue | $61.00 | |
| 4 | North | Pear | 6 | $1.50 | Average price per item | $1.36 | |
| 5 | East | Apple | 8 | $1.20 | |||
| 6 | South | Apple | 12 | $1.20 | |||
| 7 | North | Plum | 5 | $2.00 |
North sold 10 apples at 1.50 and 5 plums at 31.00. The plain average of the prices would treat a plum as often sold as an apple; G4 weights each price by its quantity.
Practice: revenue with a condition
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Qty | Price | Formula | Result | |
| 2 | North | Apple | 10 | $1.20 | South revenue | ||
| 3 | South | Pear | 4 | $1.50 | |||
| 4 | North | Pear | 6 | $1.50 | |||
| 5 | East | Apple | 8 | $1.20 | |||
| 6 | South | Apple | 12 | $1.20 | |||
| 7 | North | Plum | 5 | $2.00 |
Your turn: Calculate the South revenue: quantity times price, only for the South rows. Write the formula in G2.
SUMPRODUCT vs SUMIFS, and its two errors
| Condition | SUMIFS | SUMPRODUCT |
|---|---|---|
| Column equals a value | =SUMIFS(C2:C7,A2:A7,"North") | =SUMPRODUCT((A2:A7="North")*C2:C7) |
| Contains text | =SUMIFS(C2:C7,B2:B7,"*app*") | =SUMPRODUCT(ISNUMBER(SEARCH("app",B2:B7))*C2:C7) |
| Month of a date | not possible directly | =SUMPRODUCT((MONTH(B2:B7)=2)*D2:D7) |
| Column vs column | not possible | =SUMPRODUCT(--(D2:D7>C2:C7)) |
| Quantity × price | not possible | =SUMPRODUCT(C2:C7,D2:D7) |
Prefer SUMIFS whenever it can do the job. It reads better, it is faster on tens of thousands of rows, and it accepts whole columns. =SUMPRODUCT((A:A="North")*C:C) multiplies over a million rows and returns #VALUE! as soon as it reaches the header text in C1, so give SUMPRODUCT exact ranges like A2:A500.
The two errors you will meet:
#VALUE!from ranges of different sizes.=SUMPRODUCT(B2:B6,C2:C7)fails. Every range must cover the same rows.#VALUE!from text in a multiplied range. A header or an "n/a" insideC2:C7breaks(A2:A7="North")*C2:C7, because text cannot be multiplied. Start the range below the header, or pass the values as a separate argument:=SUMPRODUCT(--(A2:A7="North"),C2:C7)treats text in C as 0.
Frequently Asked Questions
What does SUMPRODUCT do in Excel?
It multiplies ranges row by row and adds the products. =SUMPRODUCT(B2:B6,C2:C6) is B2C2 + B3C3 + ... + B6*C6, for example quantity times price summed into an order total.
How do I use SUMPRODUCT with a condition?
Multiply by a comparison: =SUMPRODUCT((A2:A7="North")*C2:C7) adds C2:C7 for the North rows. The comparison gives TRUE or FALSE, which become 1 or 0 when multiplied.
What does -- mean in SUMPRODUCT?
It is two minus signs, which turn TRUE and FALSE into 1 and 0. =SUMPRODUCT(--(C2:C7>50)) counts the values over 50. Without it, SUMPRODUCT treats TRUE/FALSE as 0 and returns 0.
Should I use SUMPRODUCT or SUMIFS?
Use SUMIFS when its criteria can express the condition: it is easier to read and faster on large ranges. Use SUMPRODUCT when the condition needs a calculation, such as the month of a date, one column compared with another, or quantity times price.
Why does SUMPRODUCT return #VALUE!?
The ranges have different sizes (B2:B6 with C2:C7), or a range multiplied with * contains text. Make every range the same size, and pass ranges with text as separate arguments, which treats text as 0.
