Weighted average in Excel: the SUMPRODUCT ÷ SUM formula

The weighted average formula and SUMPRODUCT

Mathematically, the weighted average is Σ(weight × value) ÷ Σ(weight). Excel has no WEIGHTED.AVERAGE function, but SUMPRODUCT does the top half in one step: =SUMPRODUCT(array1, array2, …) multiplies the arrays element by element and adds the results. With the quantities in C2:C7 as weights and the prices in D2:D7 as values, the formula is:

=SUMPRODUCT(C2:C7,D2:D7)/SUM(C2:C7)

The order of the two ranges inside SUMPRODUCT does not matter, because multiplication is commutative. The denominator does matter: it must be the sum of the weights, never the sum of the values and never the number of rows. Both ranges must have the same size, otherwise SUMPRODUCT returns #VALUE!. Text inside the ranges is treated as zero, which silently lowers the result, so check imported numbers before you trust the answer.

Step by step: the average purchase price

If you prefer to see every step, use a helper column. Line cost in E2 is =C2*D2, filled down: 1,000, 2,520, 700, 1,980, 1,440 and 465. The total cost is 8,105 and the total quantity 3,500, so the weighted average is 8,105 ÷ 3,500 = 2.3157. The one-cell SUMPRODUCT formula gives exactly the same number without the helper column; the helper column is still useful for auditing, because anyone can follow the multiplication row by row.

The simple average (2.10 + 2.20 + 2.40 + 2.50 + 2.80 + 3.10) ÷ 6 = 2.5167 answers a different question: what is the typical price on an order line, regardless of size. For costing, budgeting or margin calculations that number is wrong, because the expensive orders were small. The two averages are only equal when all weights are the same, and the gap grows the more the weights differ and the more the values and weights are related. Here the biggest orders had the lowest prices, which pulls the weighted figure down.

Weights in percent: grades and scorecards

When the weights are percentages that add up to 100%, SUMPRODUCT on its own already gives the weighted average: 20% × 92 + 30% × 74 + 50% × 81 = 18.4 + 22.2 + 40.5 = 81.1. Dividing by SUM(D18:D20) anyway costs nothing and protects you when the weights do not add up. The simple average of the three scores is 82.3, higher, because the weak midterm and the heavy final exam are no longer weighted correctly.

Dividing by the sum of the weights also rescales automatically. If the final exam has not been written yet and its cells are empty, SUMPRODUCT covers only homework and midterm: (18.4 + 22.2) ÷ 50% = 81.2, the weighted average of the work done so far. Keep a check cell that sums the weights, as in D21, so a typo such as 3% instead of 30% is visible at once. The same pattern drives scorecards and decision tables, see the weighted decision matrix.

Worked example: average purchase price and a weighted grade

Units, unit prices, grade weights and scores are inputs (yellow). The line cost is filled down; the summary compares the weighted and the simple average and splits the weighted price by supplier.

Worked example: average purchase price and a weighted grade

	A	B	C	D	E
1	Order	Supplier	Units	Unit price	Line cost
2	PO-101	North	400	2.50	1,000.00
3	PO-102	South	1,200	2.10	2,520.00
4	PO-103	North	250	2.80	700.00
5	PO-104	South	900	2.20	1,980.00
6	PO-105	North	600	2.40	1,440.00
7	PO-106	South	150	3.10	465.00
8
9	Purchase summary				Value
10	Total units				3,500
11	Total cost				8,105.00
12	Weighted average price = SUMPRODUCT ÷ SUM				2.3157
13	Simple AVERAGE of the six prices (misleading here)				2.5167
14	Weighted average, North orders only				2.5120
15	Weighted average, South orders only				2.2067
16
17	Course grade			Weight	Score
18	Homework			20%	92
19	Midterm exam			30%	74
20	Final exam			50%	81
21	Weights add up to			100%
22	Weighted grade				81.1
23	Simple average of the three scores				82.3

Fictional purchase orders and grades. The same formula works for any value and weight pair: price and quantity, score and credit hours, rate and volume.

Weighted average with a condition

To average only the rows that meet a criterion, add the condition as a third array inside SUMPRODUCT and use SUMIFS for the matching weights:

=SUMPRODUCT((B2:B7="North")*C2:C7*D2:D7)/SUMIFS(C2:C7,B2:B7,"North")

The comparison (B2:B7="North") returns TRUE or FALSE for each row; multiplying turns them into 1 and 0, so only North rows contribute. North pays 3,140 for 1,250 units, a weighted average of 2.512; South pays 4,965 for 2,250 units, 2.2067. With a helper column for line cost, two SUMIFS do the same job: =SUMIFS(E2:E7,B2:B7,"South")/SUMIFS(C2:C7,B2:B7,"South"). More conditions are added as further factors, as explained in SUMPRODUCT with multiple criteria.

Averaging percentages, rates and averages

The most common hidden weighted-average problem is averaging rates. Shop A converts 2% of 10,000 visits, shop B 8% of 500 visits. The simple average of the two rates is 5%, but the combined conversion rate is (200 + 40) ÷ 10,500 = 2.29%. The rule: a rate, margin, average price or average grade must be weighted by its base, which is the same as dividing the total of the numerators by the total of the denominators.

The same applies to averages of averages. If one class of 28 students averages 4.6 and another of 12 averages 5.2, the overall average is (28 × 4.6 + 12 × 5.2) ÷ 40 = 4.78, not 4.9. Whenever a column already contains ratios, ask what the denominator was and weight by it.

Which average to use

Use AVERAGE when every row counts equally, such as daily temperatures or survey answers on the same scale. Use AVERAGEIF or AVERAGEIFS when every row counts equally but only some rows qualify. Use SUMPRODUCT ÷ SUM whenever the rows stand for different amounts: quantities, credit hours, visits, balances or market values. Inventory costing uses a running version of the same idea, covered in FIFO, LIFO and weighted average cost.

Common mistakes

• Dividing by the number of rows. =SUMPRODUCT(C2:C7,D2:D7)/COUNT(D2:D7) returns 1,350.83, not a price. The denominator must be the sum of the weights.
• Ranges of different sizes. SUMPRODUCT(C2:C7, D2:D8) returns #VALUE!. Select the same rows for values and weights.
• Numbers stored as text. SUMPRODUCT treats text as zero, so an imported quantity like "400" simply disappears from the total without an error.
• Averaging percentages directly. Conversion rates, margins and average prices must be weighted by their base, otherwise small groups get far too much influence.
• Weights that do not add up. If percentage weights add to 90% or 110% and you use SUMPRODUCT without dividing by SUM, the result is scaled wrong. Add a check cell.
• Unlocked ranges when copying. When the formula is copied to other rows or columns, lock the ranges with $ or the weights slide away from the values.