Weighted average of the finals only (SUMPRODUCT)
The problem
Problem statement
The gradebook in A2:C5 mixes quizzes and finals, each with its own weight. The board wants one number: the **weighted** average of the Final rows alone. Fill the Final grade cell E2 — the quiz weights must not drag it down.
Divide by the filtered weight sum, not by the total of all weights — using every weight gives 52 instead of 86 in case 1.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | E | |
|---|---|---|---|---|
| 1 | Type | Score | Weight | Final grade |
| 2 | Final | 90 | 0.3 | |
| 3 | Quiz | 70 | 0.3 | |
| 4 | Final | 80 | 0.2 | |
| 5 | Quiz | 88 | 0.2 |
Notes
A comparison like (A2:A5="Final") returns TRUE/FALSE, and inside SUMPRODUCT those behave as 1/0, so multiplying by the condition keeps only the rows you want. The denominator has to use the same condition, because averaging a subset means renormalising its weights. AVERAGEIF cannot do this — it averages values, it does not apply weights.
How to approach
Your answer goes in E2. On submit we re-run your formula against 3 different sets of input, so a hardcoded number will fail — the formula has to follow the cells.
- Target cell is E2
- Only rows whose type in A2:A5 is
Finalcount - A comparison inside SUMPRODUCT acts as 1/0, so multiply it into the product
- Shape: SUMPRODUCT((condition)*scores*weights) / SUMPRODUCT((same condition)*weights)
Official solutions
Show 2 solutions — try it yourself first
Approach 1: condition multiplied through
=SUMPRODUCT(($A$2:$A$5="Final")*$B$2:$B$5*$C$2:$C$5)/SUMPRODUCT(($A$2:$A$5="Final")*$C$2:$C$5)
The condition zeroes out quiz rows in both the numerator and the denominator, so the maths only ever sees the finals: (90×0.3 + 80×0.2) ÷ (0.3 + 0.2) = 86.
Approach 2: comma syntax with -- coercion
=SUMPRODUCT(($A$2:$A$5="Final")*$B$2:$B$5,$C$2:$C$5)/SUMPRODUCT(--($A$2:$A$5="Final"),$C$2:$C$5)
SUMPRODUCT can take several arrays separated by commas; the double minus turns TRUE/FALSE into 1/0 before the weights are multiplied in. Same answer, and easier to read once the condition list grows.
FAQ
What does SUMPRODUCT do with a comparison like A2:A5="Final"?
It turns the comparison into 1s and 0s. Multiplying by it keeps the matching rows and zeroes the rest, which is why one formula can both filter and compute.
Why divide by the weight of the finals only?
Because you are averaging a subset. The finals carry 0.5 of the total weight, so dividing by 1.0 would shrink the answer. Dividing by the same filtered weight renormalises it — that is the difference between 86 and 43.
Why not just use AVERAGEIF?
AVERAGEIF averages values, it cannot apply a second column of weights. A weighted average needs the products (score × weight), which is exactly what SUMPRODUCT multiplies for you.