How to count correct answers against an answer key in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--(B3:F3=B$2:F$2))Compare each student's row with the locked key row: =SUMPRODUCT(--(B3:F3=B$2:F$2)), filled down.
- Result on the example
- one result per row in
G3, filled down toG8 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in G3, filled down to G8. Change any input and the result updates, or edit the formula to experiment.
How it works
B3:F3=B$2:F$2 compares the five answers with the five key answers, position by position, and returns TRUE for each match. SUMPRODUCT counts them. The $ before the 2 keeps the key row fixed while the student row moves down.
Finn left Q5 blank, which doesn't match B, so he scores 4.
=SUMPRODUCT(--(B3:F3=B$2:F$2))Result5
B3:F31 row × 5 cols: "B", "D", "A", "C", …A range: 5 cells in one row.
B$2:F$21 row × 5 cols: "B", "D", "A", "C", …A range: 5 cells in one row.
B3:F3=B$2:F$2{1 row × 5 cols} TRUE, TRUE, TRUE, TRUE, …The = operator tests whether the two sides are equal.
-(B3:F3=B$2:F$2{1 row × 5 cols} -1, -1, -1, -1, …Makes the value negative.
--(B3:F3=B$2:F$2{1 row × 5 cols} 1, 1, 1, 1, …Makes the value negative.
SUMPRODUCT(--(B3:F3=B$2:F$2))5SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--(B3:F3=B$2:F$2{1 row × 5 cols} 1, 1, 1, 1, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--(B3:F3=B$2:F$2)). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(--(B3:F3=B$2:F$2)) | 5, 3, 3… | Excel 2021, Microsoft 365 and Google Sheets handle the array in SUM. |
| =SUMPRODUCT(--EXACT(B3:F3,B$2:F$2)) | 5, 3, 3… | Case-sensitive: "b" would not match "B". Same result here. |
Common mistakes
- Without the $ in
B$2:F$2, the key moves down as you fill and each student is compared with the row above. - Answers with stray spaces ("B ") don't match. TRIM imported form responses first.
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.
Count correct answers against an answer key
G3, count how many of the student's answers match the key in row 2, then fill down to G8.Questions
How do I show the score as a percentage?
Divide by the number of questions: =SUMPRODUCT(--(B3:F3=B$2:F$2))/COLUMNS(B$2:F$2).
How do I count how many students got each question right?
Count down the column: =COUNTIF(B3:B8,B$2), filled across.
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.