# How to count correct answers against an answer key in Excel

Compare each student's row with the locked key row: `=SUMPRODUCT(--(B3:F3=B$2:F$2))`, filled down.

**Excel:** `=SUMPRODUCT(--(B3:F3=B$2:F$2))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Student | Q1 | Q2 | Q3 | Q4 | Q5 | Score |
| Key | B | D | A | C | B |  |
| Ava | B | D | A | C | B |  |
| Ben | B | C | A | C | D |  |
| Chloe | A | D | A | B | B |  |
| Dan | B | D | C | C | B |  |
| Eve | C | A | B | D | A |  |
| Finn | B | D | A | C |  |  |

Formula in G3, filled to G8: `=SUMPRODUCT(--(B3:F3=B$2:F$2))`

Result: 5; 3; 3; 4; 0; 4

## 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.

## Other ways

- `=SUM(--(B3:F3=B$2:F$2))`: Excel 2021, Microsoft 365 and Google Sheets handle the array in SUM.
- `=SUMPRODUCT(--EXACT(B3:F3,B$2:F$2))`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-correct-answers-against-a-key