What it does
In its simplest form, =SUMPRODUCT(B2:B6,C2:C6) multiplies quantity by price on each row and adds the results, so there is no need for a separate Amount column. All ranges must have the same number of rows and columns; cells that are not numbers count as 0.
Its real strength is conditions. A comparison such as (B2:B6="West") gives TRUE or FALSE for each row, and when multiplied by a number TRUE becomes 1 and FALSE 0. So =SUMPRODUCT((B2:B6="West")*D2:D6) adds only the West rows. The condition can use a function too: (MONTH(A2:A6)=10) filters a date column by month without a helper column, which SUMIFS cannot do directly.
Multiplying conditions with * means AND; adding them with + means OR. With OR, test the sum with >0 so that no row is counted twice: ((B2:B6="West")+(B2:B6="South")>0). For simple conditional totals SUMIFS is faster and easier to read; keep SUMPRODUCT for the cases it cannot handle.
Syntax
=SUMPRODUCT(array1, [array2], ...)=TOPLA.ÇARPIM(dizi1; [dizi2]; ...)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
array1 | Required | The first range or condition expression to multiply. With a single array, it is simply added up. |
array2 | Optional | Further ranges or expressions, all the same size as array1. Up to 255 arrays. |
Examples
1Order total from quantity and unit price
There is no Amount column; the formula in F2 multiplies quantity by unit price on each row and adds the products.
=SUMPRODUCT(B2:B6,C2:C6)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Qty | Unit Price | Order Total | ||
| 2 | Copper cable (m) | 120 | 52.00 | 13,845.00 | ||
| 3 | Grounded socket | 25 | 92.50 | |||
| 4 | Circuit breaker | 10 | 132.00 | |||
| 5 | Cable duct | 40 | 68.00 | |||
| 6 | Light switch | 30 | 41.75 |
The products of the five rows add up to 13,845.00. You would get the same by adding an Amount column and summing it; SUMPRODUCT does it in one cell.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Sum by month and region
There is no separate month column. MONTH(A2:A8)=E2 tests the month of each row and B2:B8=F2 its region; multiplied by the amounts, only the rows that meet both conditions remain.
=SUMPRODUCT((MONTH(A2:A8)=E2)*(B2:B8=F2)*C2:C8)| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Date | Region | Amount | Month | Region | Total | |
| 2 | 28-Sep-2025 | West | 4,200.00 | 10 | West | 20,350.50 | |
| 3 | 2-Oct-2025 | West | 12,500.00 | ||||
| 4 | 6-Oct-2025 | North | 8,300.00 | ||||
| 5 | 14-Oct-2025 | West | 4,750.50 | ||||
| 6 | 21-Oct-2025 | South | 9,900.00 | ||||
| 7 | 30-Oct-2025 | West | 3,100.00 | ||||
| 8 | 3-Nov-2025 | West | 15,200.00 |
The three West rows in October are added: 20,350.50. Type 11 in E2 to see the West's November total.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Weighted average unit cost of purchases
The same product was bought in different quantities at different prices. A simple average counts every purchase equally; a weighted average gives large purchases more weight.
=SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5)| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Purchase | Qty | Unit Price | Weighted Average | Simple Average | ||
| 2 | January | 1000 | 48.00 | 50.47 | 52.75 | ||
| 3 | February | 200 | 55.00 | ||||
| 4 | March | 1500 | 50.00 | ||||
| 5 | April | 300 | 58.00 |
- F2
=SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5) - G2
=AVERAGE(C2:C5)
The true average cost per unit is 50.47; the simple average is 52.75, because it counts the small, expensive purchases as much as the large ones.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #VALUE!
Cause: The ranges are not the same size (
B2:B6againstC2:C7).Fix: Start and end every range on the same rows.
- Result is 0
Cause: The condition was passed as a separate argument:
=SUMPRODUCT(B2:B6="West",D2:D6). TRUE/FALSE values are not turned into numbers there, so everything counts as 0.Fix: Multiply the condition in, or put two minus signs in front of it:
=SUMPRODUCT(--(B2:B6="West"),D2:D6).- Slow workbook
Cause: Whole-column references such as
A:Amake it multiply more than a million rows one by one.Fix: Limit the ranges to where the data ends:
A2:A500.
Good to know
- For a weighted average, divide the sum of the products by the sum of the weights:
=SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6). - To count rows that meet the conditions, leave out the amount column and multiply only the conditions:
=SUMPRODUCT((B2:B6="West")*(D2:D6>1000)).
In Nexell
Get Nexell to do it from a sentence
Calculate the grand total of the order from the quantity and unit price columns in a single cell
Convert to tableBring your order table into the tool and type this. Nexell builds the total with a real Excel formula and shows it in the preview first; in the downloaded file, the total changes when a quantity or price changes.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
What is the difference between SUMPRODUCT and SUMIFS?
SUMIFS works with ready-made criteria (equals, greater than, wildcards) and is faster on large lists. SUMPRODUCT can use any expression as a condition: a month, a year, part of a text, or an OR between conditions. Pick SUMIFS for simple jobs and SUMPRODUCT for flexible conditions.
How do I calculate a weighted average with SUMPRODUCT?
Multiply the values by their weights, add them up and divide by the total weight: =SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6). If B is quantity and C unit price, the result is the average cost per unit.