Math & Trig
Score quiz or exam answers automatically against an answer key
Compare student response rows against a locked answer-key row inside SUMPRODUCT for both equal-point and weighted-question exams.
Element-by-element answer comparison with SUMPRODUCT
Comparing a student's response row B4:F4 = $B$2:$F$2 produces a horizontal array of TRUE/FALSE flags—one per question. Applying double unary -- turns TRUE into 1 and FALSE into 0 so SUMPRODUCT totals correct answers.
Passing a question-weight row ($B$3:$F$3) as the second argument of SUMPRODUCT awards variable points per question (e.g., 3 points for hard Question 4 vs 1 point for Question 1).
| RowRole (A) | Q1 (B) | Q2 (C) | Q3 (D) | Q4 (E) | Q5 (F) |
|---|---|---|---|---|---|
| AnswerKey | B | D | A | C | B |
| Weights | 1 | 2 | 2 | 3 | 2 |
| Student1 | B | D | C | C | B |
Raw correct count, percentage, and weighted points
Student1 matches Q1, Q2, Q4, and Q5 (4 of 5 raw = 80%), earning 1 + 2 + 0 + 3 + 2 = 8 of 10 weighted points.
=SUMPRODUCT(--(B4:F4 = $B$2:$F$2), --($B$2:$F$2<>""))Weighted points formula
Lock both $B$2:$F$2 and $B$3:$F$3 with $ signs before copying down a class roster.
=SUMPRODUCT(--(B4:F4 = $B$2:$F$2), --($B$2:$F$2<>""), $B$3:$F$3)Inputs and interpretation
Only questions with a nonempty answer key count. Blank response/key pairs receive no point. Equality is case-insensitive; normalize spaces deliberately. Use numeric nonnegative weights. The percentage denominator counts active keys and must be positive; an entirely empty quiz has no defined percentage.
Try the example
Use the original values or change them to test your understanding.