Skip to content

Excel function · Math

Excel SUMPRODUCT function

SUMPRODUCT multiplies the matching cells of ranges of the same size and adds the products: an order total from quantity and unit price in one formula. Written as factors, conditions let it build totals that SUMIFS cannot.

In Turkish Excel: TOPLA.ÇARPIM

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

English Excel=SUMPRODUCT(array1, [array2], ...)
Turkish Excel=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

ArgumentRequiredDescription
array1RequiredThe first range or condition expression to multiply. With a single array, it is simply added up.
array2OptionalFurther 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.

F2fx=SUMPRODUCT(B2:B6,C2:C6)
Order total from quantity and unit price: example table
ABCDEF
1ProductQtyUnit PriceOrder Total
2Copper cable (m)12052.0013,845.00
3Grounded socket2592.50
4Circuit breaker10132.00
5Cable duct4068.00
6Light switch3041.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.

G2fx=SUMPRODUCT((MONTH(A2:A8)=E2)*(B2:B8=F2)*C2:C8)
Sum by month and region: example table
ABCDEFG
1DateRegionAmountMonthRegionTotal
228-Sep-2025West4,200.0010West20,350.50
32-Oct-2025West12,500.00
46-Oct-2025North8,300.00
514-Oct-2025West4,750.50
621-Oct-2025South9,900.00
730-Oct-2025West3,100.00
83-Nov-2025West15,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.

F2fx=SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5)
Weighted average unit cost of purchases: example table
ABCDEFG
1PurchaseQtyUnit PriceWeighted AverageSimple Average
2January100048.0050.4752.75
3February20055.00
4March150050.00
5April30058.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:B6 against C2: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:A make 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

Type this in Nexell

Calculate the grand total of the order from the quantity and unit price columns in a single cell

Convert to table

Bring 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.

Related functions

All math functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free