Weighted Average in Excel — SUMPRODUCT ÷ SUM (and Why AVERAGE of Averages Lies)

TL;DR — Excel has no WEIGHTEDAVG function; the idiom is =SUMPRODUCT(values, weights)/SUM(weights). SUMPRODUCT multiplies each value by its weight and adds the results (the weighted total); dividing by SUM(weights) turns that total back into a per-unit average. You need it whenever your numbers carry different importance — grades weighted by credit hours, prices weighted by quantity sold, returns weighted by amount invested. The error it fixes is the most common average mistake there is: taking a plain AVERAGE of figures that each summarize a different-sized group, which gives a tiny group and a huge group equal say and produces a confidently wrong number.

=SUMPRODUCT(B2:B10, C2:C10)/SUM(C2:C10)   ' values in B, weights in C
=SUMPRODUCT(Scores, Credits)/SUM(Credits) ' GPA: grade points weighted by credits
=SUMPRODUCT(B2:B10, C2:C10)               ' shortcut ONLY if weights already sum to 1

Most pages that rank for "weighted average Excel" hand you =SUMPRODUCT(...)/SUM(...) and a worked GPA and stop. But the formula was never the hard part — you can memorize it in a minute. The hard part is knowing when a plain average is quietly wrong and a weighted one is required, and why the two can differ so much that they tell opposite stories. That judgment is what saves a report, so that's what this page leads with.

What you'll learn

The mental model: a weight is how many "votes" each value gets
Why the formula is SUMPRODUCT ÷ SUM, term by term
The core error: why AVERAGE of group summaries gives the wrong answer
The shortcut when weights already add up to 1 (or 100%)
Real patterns: GPA, portfolio return, blended price
The gotchas: text in the ranges, mismatched lengths, and a conditional weighted average

The mental model: weights are votes

A plain average gives every value one vote. A weighted average lets each value vote in proportion to a weight — a quantity, a headcount, a dollar amount, a number of credit hours. A value with weight 10 pulls the result ten times as hard as a value with weight 1. That's the whole idea; everything else is bookkeeping to make the votes add up correctly.

The bookkeeping is two steps. First, multiply each value by its weight and add those up — that's SUMPRODUCT(values, weights), the total "influence." Second, divide by the total number of votes, SUM(weights), to bring it back to the same scale as a single value. Skip the second step and you have a weighted total, not a weighted average — a number that grows just because you have more data, which is rarely what you want.

Why you divide by SUM(weights)

It's worth seeing the arithmetic once, because it explains every variation later. Suppose three purchases: 2 units at $10, 3 units at $20, 5 units at $30. The average price a customer actually paid isn't (10+20+30)/3 = 20 — that pretends one unit was bought at each price. It's the total money divided by the total units:

' Prices in B2:B4 = 10, 20, 30   |   Quantities in C2:C4 = 2, 3, 5
=SUMPRODUCT(B2:B4, C2:C4)   ' -> 20 + 60 + 150 = 230   (total money spent)
=SUM(C2:C4)                 ' -> 2 + 3 + 5    = 10      (total units bought)
=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)  ' -> 230 ÷ 10 = 23  (blended unit price)

The plain average is 20; the weighted average is 23, because more units sold at the higher price. SUMPRODUCT builds the numerator (money), SUM builds the denominator (units), and the quotient is the price per unit that actually occurred. Every weighted average is this same money-over-units shape, whatever the "money" and "units" happen to be.

The core error: AVERAGE of averages

This is the mistake weighted averages exist to prevent, and it's everywhere. Say three regions report their average order value, and you want the company-wide average:

' Regional averages in B: 50, 40, 90   |   Order counts in C: 300, 250, 3
=AVERAGE(B2:B4)                       ' -> 60    (WRONG: each region counts equally)
=SUMPRODUCT(B2:B4, C2:C4)/SUM(C2:C4)  ' -> 45.6  (RIGHT: weighted by order count)

The plain AVERAGE says 60. But one region has 3 orders and another has 300, and the plain mean gives them identical weight — the 3-order outlier yanks the company number up by 14 points on the strength of almost nothing. The weighted average, 45.6, reflects where the orders actually are. Whenever you find yourself averaging numbers that are already averages (or rates, or per-unit figures) of groups that aren't the same size, a plain AVERAGE is almost certainly the wrong tool. Ask: do these numbers each stand for a different-sized pile of underlying data? If yes, weight them.

The shortcut: when weights already sum to 1

If your weights are proportions that already add up to 1 (or 100%) — an asset allocation, a scorecard rubric, a probability distribution — then SUM(weights) is 1 and dividing by it changes nothing. You can drop the denominator:

' Weights in C already sum to 100%: 0.5, 0.3, 0.2
=SUMPRODUCT(B2:B4, C2:C4)   ' -> weighted average directly, no ÷ needed

This is the form you'll see in portfolio returns and weighted scorecards. One caution: it's only valid if the weights truly sum to 1. If a stray rounding leaves them at 0.99, the bare SUMPRODUCT is silently off. Keeping the /SUM(weights) costs nothing and is self-correcting, so use the full form unless you're certain — it can never be wrong.

Real patterns worth memorizing

The same skeleton covers most real jobs:

' GPA — grade points weighted by credit hours
=SUMPRODUCT(GradePoints, Credits)/SUM(Credits)

' Portfolio return — each holding's return weighted by its dollar value
=SUMPRODUCT(Returns, MarketValues)/SUM(MarketValues)

' Blended interest rate across loans of different balances
=SUMPRODUCT(Rates, Balances)/SUM(Balances)

' Survey score weighted by number of respondents per group
=SUMPRODUCT(GroupScores, Respondents)/SUM(Respondents)

In every one, the second range is the "how much does this row matter" column. If you can name that column, you can write the weighted average.

The gotchas: text, length, and conditions

SUMPRODUCT is less forgiving than AVERAGE. Where AVERAGE skips text, SUMPRODUCT returns #VALUE! the moment either range holds text it can't multiply — so clean the ranges first, or coerce with IF/-- if some cells are deliberately non-numeric. The two ranges must also be the same length; mismatched heights give #VALUE! because there's no partner to multiply. And an empty or all-zero weight column makes SUM(weights) zero, so the whole thing returns #DIV/0! — wrap it with IFERROR if that's possible.

For a conditional weighted average — say, only the "West" region — put the condition inside both SUMPRODUCTs as a (condition) array that evaluates to 1s and 0s:

=SUMPRODUCT((Region="West")*Value*Weight)/SUMPRODUCT((Region="West")*Weight)

The (Region="West") term zeroes out every non-matching row in both the numerator and the denominator, so only the West rows contribute — the weighted cousin of AVERAGEIFS, which can only do an unweighted conditional mean. See the SUMPRODUCT guide for how the condition-as-array trick works in general.

How ExcelMaster helps

The trap with weighted averages isn't writing SUMPRODUCT/SUM — it's spotting that you needed a weighted average in the first place, back when you'd already typed =AVERAGE(...) over a column of regional means. Ask ExcelMaster for "the average order value across all regions" and, seeing that each row is itself an average of a different number of orders, it writes the weighted version and tells you how far off the plain mean would have been. Say "blended price across these purchases" or "my GPA from this transcript" and it wires up =SUMPRODUCT(...)/SUM(...) with the right value and weight columns already mapped.

Frequently asked questions

How do I calculate a weighted average in Excel?

Use =SUMPRODUCT(values, weights)/SUM(weights). SUMPRODUCT multiplies each value by its weight and adds the products (the weighted total); dividing by SUM(weights) converts that total into a per-unit average. For example, =SUMPRODUCT(B2:B10, C2:C10)/SUM(C2:C10) averages the values in B weighted by the amounts in C.

Why not just use AVERAGE?

Because AVERAGE gives every value equal weight. When your numbers summarize different-sized groups — regional averages, per-unit prices, grades across different credit hours — equal weighting lets a tiny group count as much as a huge one, producing a misleading result. A weighted average lets each value count in proportion to its size.

Do I always divide by SUM(weights)?

Only when the weights don't already add up to 1. If your weights are proportions summing to 1 (or 100%), SUM(weights) is 1 and the division does nothing, so =SUMPRODUCT(values, weights) alone is enough. When in doubt, keep the /SUM(weights) — it's always correct and self-correcting if the weights don't sum exactly to 1.

Why does my weighted average return #VALUE! or #DIV/0!?

#VALUE! usually means one of the ranges contains text SUMPRODUCT can't multiply, or the value and weight ranges are different lengths — they must match cell for cell. #DIV/0! means SUM(weights) came out to zero (an empty or all-zero weight column). Clean the ranges and confirm the weights add up to a positive number.

How do I do a weighted average with a condition?

Put the condition inside both SUMPRODUCTs as an array: =SUMPRODUCT((Region="West")*Value*Weight)/SUMPRODUCT((Region="West")*Weight). The (Region="West") term becomes 1 for matching rows and 0 otherwise, so only those rows count in the numerator and denominator alike.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-27.