SUMPRODUCT in Excel: weighted average and totals with criteria

In short: =SUMPRODUCT(B2:B7,C2:C7) calculates B2*C2 + B3*C3 + … + B7*C7. A weighted average is =SUMPRODUCT(B2:B7,C2:C7)/SUM(B2:B7). A total with a condition is =SUMPRODUCT((A2:A7="North")*B2:B7*C2:C7).

Try it now

An office supplies order with six lines: quantities in column B, unit prices in column C. Get the order total without adding a helper column.

Task In C9, calculate the total value of the order: each quantity times its price, all added up. Do it with a single function and no helper column.

	A	B	C
1	Item	Qty	Price $
2	Printer paper	12	4.99
3	Toner	3	59.9
4	Binder	40	2.49
5	Ballpoint pen	100	0.79
6	Sticky notes	24	1.89
7	USB stick	6	8.5
8
9	Order total $

The SUMPRODUCT formula

Argument	Meaning	In the example
array1	The first range, such as the quantities	B2:B7
array2	The second range, the same size as the first	C2:C7
array3 …	Optional further ranges that are multiplied in as well, up to 255 in total	none

Excel pairs up the values in the same position, B2 with C2, B3 with C3 and so on, multiplies each pair and adds the products. With a single range, SUMPRODUCT simply adds it up like SUM. Microsoft documents that text and other non-numeric entries count as zero.

How to calculate a weighted average in Excel

AVERAGE gives every value the same say. That is wrong when some values matter more than others: a price paid on 1,500 units tells you more about your real cost than a price paid on 200. A weighted average multiplies each value by its weight, adds those up, and divides by the total weight.

Part	Formula	What it is
Top	SUMPRODUCT(B2:B7,C2:C7)	weight times value, summed: the total amount
Bottom	SUM(B2:B7)	the sum of the weights, here the total quantity

The same pattern gives you a weighted grade, an interest rate weighted by balance, or delivery time weighted by order size. If the weights already add up to exactly 1 or 100%, you can leave the division out, but check that with SUM first.

Practice: weighted average purchase price

The same item arrived in six deliveries, some big and some small, at different prices. What did one unit really cost on average?

Task An item was bought six times at different prices. In C9, calculate the average purchase price per unit weighted by quantity, so large deliveries count more than small ones.

	A	B	C
1	Delivery	Units	Unit price $
2	January	500	2.1
3	February	1200	1.95
4	March	300	2.4
5	April	800	2.05
6	May	1500	1.89
7	June	200	2.55
8
9	Weighted avg price $

This exercise is part of Premium.

Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.

See Premium Already Premium? Log in

The first exercise in this lesson is free.

SUMPRODUCT IF: totals with criteria

A comparison such as A2:A7="North" returns TRUE or FALSE for every row. Multiply it by a number range and Excel treats TRUE as 1 and FALSE as 0, so rows that fail the test drop out:

=SUMPRODUCT((A2:A7="North")*B2:B7*C2:C7) returns quantity times price, summed only for the North region.

For multiple criteria, add another bracket: =SUMPRODUCT((A2:A7="North")*(D2:D7="Toner")*B2:B7*C2:C7). A row counts only when both tests are TRUE, because 1*0 is 0. For OR logic, add the tests and keep anything above zero: ((A2:A7="North")+(A2:A7="South")>0).

Why not just use SUMIFS? SUMIFS can only add up a column that already exists. When you first need to calculate something, like quantity times price, or when the condition itself is a calculation such as MONTH(E2:E7)=3, SUMPRODUCT does it in one cell. Counting works the same way: =SUMPRODUCT(--(A2:A7="North")). The double minus turns TRUE and FALSE into 1 and 0.

SUMPRODUCT not working? Common errors

#VALUE! from ranges of different sizes

Every range must have the same number of rows and columns. =SUMPRODUCT(B2:B7,C2:C8) returns #VALUE!.

A result of 0 when the condition is its own argument

=SUMPRODUCT((A2:A7="North"),C2:C7) returns 0, because SUMPRODUCT counts anything that is not a number as zero, TRUE and FALSE included. Join the condition with * or put a double minus in front: =SUMPRODUCT(--(A2:A7="North"),C2:C7).

Numbers stored as text

Quantities imported as text count as zero, and the total comes out too low. Numbers that sit on the left of the cell are the usual giveaway.

Whole columns like A:A

Microsoft advises against full-column references here because SUMPRODUCT then processes over a million cells per column. Use fixed ranges or an Excel table.

SUMPRODUCT in German Excel

English	German
=SUMPRODUCT(B2:B7,C2:C7)	=SUMMENPRODUKT(B2:B7;C2:C7)
SUM	SUMME
weighted average	gewichteter Mittelwert

SUMPRODUCT in Google Sheets

Google Sheets has SUMPRODUCT with the same syntax, and the weighted average formula and the multiply-by-condition trick work there too.

Which Excel versions have SUMPRODUCT?

All current ones: Microsoft 365, Excel 2024, 2021, 2019 and 2016, on Windows and Mac. SUMPRODUCT works with whole ranges without being entered as an array formula with Ctrl+Shift+Enter, which is why it was the go-to tool for conditional sums long before dynamic arrays.

FAQ