How to Calculate a Weighted Average in Excel With SUMPRODUCT

You bought the same part three times at three prices and want the average price you paid. AVERAGE gives you a number in a second, and it is wrong whenever the purchases were different sizes. The fix is a weighted average, where each value counts in proportion to how much of it there was. In Excel, that is one short formula built from SUMPRODUCT and SUM.

1. Why a plain average misleads

Microsoft's AVERAGE page defines the function as the arithmetic mean: add the numbers, then divide by how many there are. Every value gets equal say, which is fine for a list of test scores. It is not fine when one value represents 750 units and another represents 200.

Microsoft's own weighted-average example uses three purchases:

=AVERAGE(A2:A4) returns 26.67. But most of the units were bought at 20 or 25, and only a small batch at 35, so the true average price should be lower.

2. The weighted average formula

Microsoft's page gives the formula:

=SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4)

It returns 24.66 (24.655 before rounding), matching Microsoft's documented result. Here is what each part does:

• SUMPRODUCT(A2:A4,B2:B4) multiplies each price by its units and adds the results: 20 x 500 + 25 x 750 + 35 x 200 = 10,000 + 18,750 + 7,000 = 35,750. That is the total amount spent.
• SUM(B2:B4) adds the units: 1,450.
• Dividing gives the average cost per unit: 35,750 / 1,450.

Microsoft's SUMPRODUCT page describes the function as returning "the sum of the products of corresponding ranges," and shows the same idea written longhand as =C2*D2+C3*D3+C4*D4+C5*D5. SUMPRODUCT simply saves you from typing every pair.

The difference between 26.67 and 24.66 is not a rounding detail. Multiply it by 1,450 units and the plain average overstates spending by about 2,900 in total spending (2,917 exactly).

3. Weights that are percentages

Course grades are the classic case. Suppose homework is 20 percent, the midterm 30 percent, and the final 50 percent:

=SUMPRODUCT(K2:K4,L2:L4)

This returns 84.7. Because the weights add up to 100 percent, dividing by their sum would divide by 1, so you can leave it out. The plain average, =AVERAGE(K2:K4), returns 86.33, which overstates the grade because the lowest score carries the most weight.

If your weights are points instead of percentages, such as 20, 30 and 50, keep the division: =SUMPRODUCT(K2:K4,M2:M4)/SUM(M2:M4) also returns 84.7. Dividing by SUM makes the formula work whether or not the weights add up to 1, which makes it the safer habit.

4. Averaging rates: the most common mistake

Rates and percentages are where plain averages do the most damage. Two marketing channels:

=AVERAGE(O2:O3) returns 6%. But the channels together had 20 + 10 = 30 conversions from 1,100 visits. The weighted version gets that right:

=SUMPRODUCT(N2:N3,O2:O3)/SUM(N2:N3)

This returns 2.73%. The small newsletter channel had a high rate, but it was only a tenth of the traffic. Reporting 6 percent would more than double the real result. Whenever you average rates, weight them by the size of the group each rate came from.

5. A weighted average for one category

You can add a condition inside SUMPRODUCT. Reuse the three purchases, now with regions in P2:P4 (East, West, East), prices in Q2:Q4 (20, 25, 35) and units in R2:R4 (500, 750, 200):

=SUMPRODUCT((P2:P4="East")*Q2:Q4*R2:R4)/SUMIFS(R2:R4,P2:P4,"East")

The comparison (P2:P4="East") produces TRUE or FALSE for each row, which becomes 1 or 0 when multiplied, so West rows drop out. The SUMPRODUCT part returns 17,000 (10,000 + 7,000) and SUMIFS returns 700 East units, for a weighted East price of 24.29. Microsoft's SUMPRODUCT page uses the same multiply-the-conditions pattern in its own regional sales example.

6. Errors and silent problems

Microsoft lists two behaviors to watch:

• Ranges must be the same size. =SUMPRODUCT(A2:A4,B2:B3) returns #VALUE!.
• Non-numeric entries count as zero. If one unit count reads "n/a," SUMPRODUCT treats it as 0, and SUM skips it too. The formula still returns a result, but that row has silently dropped out. In the purchase example, an "n/a" in the 200-unit row would make the formula return 23 instead of 24.66. Check that every weight is a real number.

Microsoft also advises against full-column references such as A:A in SUMPRODUCT, because Excel would multiply over a million cells in each column.

7. Key takeaways

• AVERAGE gives every value equal weight, which misleads when groups differ in size.
• The weighted average is =SUMPRODUCT(values,weights)/SUM(weights).
• Dividing by SUM works whether or not the weights add up to 100 percent.
• Average rates by weighting them with the size of each group, or you can badly overstate results.
• Mismatched ranges return #VALUE!, and text in a weight column silently counts as zero.