Most courses weight categories differently — tests 50%, homework 30%, participation 20%. SUMPRODUCT multiplies each category score by its weight and sums for the final grade in one formula.
The example
Tests 88, HW 95, Part. 100.
| A | B | |
|---|---|---|
| 1 | Category | Score · Weight |
| 2 | Tests | 88 · 50% |
| 3 | Final | → 92.0 |
The formula
The formula:
How it works
How it works:
- List each category score and its weight (as decimals that sum to 1).
SUMPRODUCT(scores, weights)multiplies pairs and adds them — the weighted average.- If weights don’t total 100%, divide by their sum:
SUMPRODUCT(s, w) / SUM(w). - Add or change a category without rewriting the formula — just extend the ranges.
Guard against weights that don’t sum to 1. A safe, self-normalizing version is =SUMPRODUCT(scores, weights) / SUM(weights) — it returns the correct weighted average whether the weights are entered as 50/30/20 or 0.5/0.3/0.2, and even if one category is dropped.
Try it: interactive demo
Scores and weights (comma-separated).
Variations
Self-normalizing
Weights any scale:
Points-based
Earned ÷ possible:
Percent grade
To percentage:
Pitfalls & errors
Weights should sum to 1. If not, divide by SUM(weights) or the grade is off.
Same length ranges. Scores and weights must align or SUMPRODUCT errors.
Decimals vs percents. 50% must be 0.5 in the math — keep formats consistent.
Practice workbook
Frequently asked questions
How do I calculate a weighted grade in Excel?
What if my category weights don't total 100%?
How do I weight points instead of percentages?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class