How to Calculate a Weighted Average in Google Sheets

Use SUMPRODUCT over SUM: =SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10) multiplies each value by its weight, then divides by the total weight.

=SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10)

B2:B10 are the values, C2:C10 the weights. SUMPRODUCT sums value×weight; dividing by SUM of weights normalises it.

How it works

A plain AVERAGE treats every value equally. A weighted average lets some values count more. SUMPRODUCT multiplies each value by its matching weight and adds the results, and dividing by the total of the weights turns that into a proper average. The two ranges must be the same size.

Variations

Weights that already sum to 1 (or 100%)

=SUMPRODUCT(B2:B10,C2:C10)

If the weights are fractions adding to 1, skip the division — they are already normalised.

Weighted grade with percentages

=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)

B = scores, C = each assignment's percentage weight; works even if the percentages don't add to 100.

Ignore blank weights

=SUMPRODUCT(B2:B10,N(C2:C10))/SUM(C2:C10)

N() turns blanks and text into 0 so a missing weight doesn't break the result.

Examples

Scenario	Formula
Prices weighted by quantity sold	`=SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10)`
Course grade from weighted assignments	`=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)`

FAQ

Why not just use AVERAGE?

AVERAGE gives every value equal importance. A weighted average lets quantities, percentages, or credits decide how much each value counts.

Do the weights have to add up to 100%?

No. Dividing by SUM of the weights normalises them automatically, so any positive numbers work as weights.

What if the ranges are different sizes?

SUMPRODUCT returns a #VALUE! error. Make the value range and the weight range exactly the same height.

Related formulas

→ See every Google Sheets formula in one cheat sheet