How to calculate a weighted grade percentage in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6)Multiply each score by its weight and divide by the total weight: =SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6).
- Result on the example
- 85.2% in
F1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F1. Change any input and the result updates, or edit the formula to experiment.
How it works
SUMPRODUCT multiplies each score by its weight and adds the results: 0.2 × 92% + 0.15 × 85% and so on, 85.15%. Dividing by the sum of the weights makes the formula correct even when the weights don't add up to exactly 100%, for example before the final exam is graded.
=SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6)Result0.8515
B2:B65 rows × 1 col: 20%, 15%, 25%, 15%, …A range: 5 cells in one column.
C2:C65 rows × 1 col: 92%, 85%, 78%, 95%, …A range: 5 cells in one column.
SUMPRODUCT(B2:B6,C2:C6)0.8515SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
B2:B65 rows × 1 col: 20%, 15%, 25%, 15%, … - array2
C2:C65 rows × 1 col: 92%, 85%, 78%, 95%, …
- array1
SUM(B2:B6)1SUM: Adds numbers and ranges. Skips text and blanks.- number1
B2:B65 rows × 1 col: 20%, 15%, 25%, 15%, …
- number1
SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6)0.8515The / operator divides.
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6). We ran it in Google Sheets mode on the same data and it returns 85.2%.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(B2:B6,C2:C6) | 85.2% | Enough when the weights always add up to 100%. |
| =AVERAGE(C2:C6) | 86.2%Different result | The unweighted average, for comparison. Returns 86.2%, treating homework and the final exam as equal. |
Common mistakes
- Weights typed as 20, 15, 25 instead of 20%, 15%, 25% give a grade 100 times too big unless you divide by SUM of the weights, which the main formula does.
- A blank score counts as 0 in SUMPRODUCT. If a category isn't graded yet, see the FAQ.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Calculate a weighted grade percentage
F1, calculate the final grade from the category scores and their weights.Questions
How do I calculate the grade so far, skipping ungraded categories?
Divide by the weights of graded categories only: =SUMPRODUCT(B2:B6,C2:C6)/SUMIF(C2:C6,"<>",B2:B6).
How do I turn the percentage into a letter grade?
Look it up: =XLOOKUP(F1,{0,0.6,0.7,0.8,0.9},{"F","D","C","B","A"},,-1).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.