Here’s a wrong number that ships in reports every day. Three products: one sells 10 units at 90% margin, two sell 5,000 units each at 12%. “Average margin: 38%” — says AVERAGE, and it’s not lying about what it did: it averaged three numbers. It’s lying about the business, where margin lives on ten thousand real units, almost all of them at 12%. The true blended margin is about 12.1%.

The disease has one name — averaging things that carry different weights — and one cure: the weighted average, whose whole machine in Excel is a single elegant function.

SUMPRODUCT: multiply pairs, then add

=SUMPRODUCT(Units, Margin) / SUM(Units)

SUMPRODUCT walks two ranges in parallel, multiplies each pair, and sums the results — 10×90% + 5,000×12% + 5,000×12% — which is precisely “total margin earned”. Divide by total units, and every unit votes once, instead of every row voting once. That’s the entire idea: AVERAGE gives each row a vote; weighted averages give each unit a vote.

Where the disease appears, the same one-liner cures it: blended interest across accounts of different sizes (weight by balance), average class score across groups of different sizes (weight by headcount), portfolio return (weight by holding), average margin by region — the percentages lesson warned you never to average percentages with different bases; this is the machine that does it right.

AVERAGE — every row votes equally 90% 12% 12% → "38%" SUMPRODUCT — every unit votes 10 @90% 5,000 @12% 5,000 @12% → 12.1% — the truth
Same three rows, two elections. The top one gives a ten-unit product a third of the say; the bottom one sizes every vote — and the answer moves by 26 points.

The second life: conditions as arithmetic

SUMPRODUCT has a folkloric second career worth understanding, because you’ll inherit it. Before SUMIFS existed, conditional sums were written as:

=SUMPRODUCT((Region="North") * (Year=2026) * Amount)

The trick: comparisons produce TRUE/FALSE, arithmetic treats them as 1/0, so multiplying by a condition zeroes out the non-matching rows. It still works, and it still does the few things SUMIFS can’t (OR-logic with +, calculations inside the condition). But the modern rule of thumb: SUMIFS for criteria, SUMPRODUCT for weights — and when you meet the old incantation in an inherited workbook, you now read it as arithmetic, not magic.

The two honesty checks

Weights must be the real quantity. Weighting margin by revenue answers a different question than weighting by units — sometimes each is right; decide which question you’re answering before choosing the column, and label the result (“unit-weighted margin”). An unlabelled weighted average invites the percentage-points argument’s cousin: two people, both correct, about different questions.

Blanks are zero-weight rows, not missing ones. SUMPRODUCT happily multiplies a blank as zero — a row with units but no margin contributes nothing to the top and full weight to the bottom, silently dragging the answer down. The checks-row instinct applies: COUNTBLANK the margin column before trusting the blend, and decide what a gap actually means.

One multiply-then-add, one division, one labelled answer. The average that respects size — and the end of the 38% that never existed.