Math
Grade multiple-choice tests against an answer key (SUMPRODUCT, FILTER & TEXTJOIN)
Score student or certification exam responses automatically with unweighted counts, custom per-question point weights, and exact missed-question lists.
Score exams in one formula—and list every missed question ID automatically!
Compare five response cells with a fixed answer key and optional nonnegative point weights. Blank responses count as missed, but blank answer keys or question labels are invalid: otherwise an unanswered item could be counted correct or omitted from the missed-item list.
Equality is not a case-sensitive grading policy. No whitespace normalization is performed. For a production assessment define allowed answers, partial credit, identity and review requirements separately. This worksheet demonstrates scoring arithmetic, not certification or exam security.
Weights may be zero or fractional but must all be numeric and nonnegative. Perfect means every response matches the key, even if a missed item has zero points; it is not inferred merely from the weighted total.
| Row / Student | Q1 (1 pt) | Q2 (2 pts) | Q3 (2 pts) | Q4 (3 pts) | Q5 (2 pts) | Grading Summary |
|---|---|---|---|---|---|---|
| Answer Key (Row 3) | A | C | B | D | A | Max Possible = 10 pts |
| Alice (Row 4) | A | C | A (Miss) | D | B (Miss) | 3/5 right | 6/10 pts | Missed: Q3, Q5 |
| Ben (Row 5) | A | C | B | D | A | 5/5 right | 10/10 pts | "Perfect" |
Weighted point score against an answer key row and weight row
Multiplies 1/0 correctness flags --(B4:F4 = $B$3:$F$3) by question weights $B$2:$F$2.
=IF(OR(COUNTBLANK(B1:F1)>0,COUNTBLANK(B3:F3)>0,COUNT(B2:F2)<>5),"Invalid inputs",IF(MIN(B2:F2)<0,"Invalid inputs",SUMPRODUCT(--(B4:F4 = $B$3:$F$3), $B$2:$F$2)))List exact missed question labels (or "Perfect" when 100% correct)
FILTER extracts headers from $B$1:$F$1 wherever student answers differ from the key.
=IF(OR(COUNTBLANK(B1:F1)>0,COUNTBLANK(B3:F3)>0,COUNT(B2:F2)<>5),"Invalid inputs",IF(MIN(B2:F2)<0,"Invalid inputs",TEXTJOIN(", ", TRUE, FILTER($B$1:$F$1, B4:F4 <> $B$3:$F$3, "Perfect"))))Try the example
Use the original values or change them to test your understanding.