Compare Student Answers and Calculate Scores in Excel
Question details
Calculate each student's percentage score by comparing a row of answers with an answer key on another sheet.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Student answers are in F2:AA2, and the corresponding correct answers are on the T1 ANS sheet.
- Observed behavior
- The user wants one formula that scores all equally weighted questions without a separate IF function for each answer.
Confirm that the 22 answers in F2:AA2 correspond in order to the 22 correct answers in 'T1 ANS'!A1:V1. Fill every answer-key cell and make sure each question has equal weight.
Use SUMPRODUCT to calculate the percentage score
Compare the student's answer row with the complete answer key and divide the number of matches by the number of questions.
Select E2 and enter =SUMPRODUCT((F2:AA2='T1 ANS'!$A$1:$V$1)*1)/COUNTA('T1 ANS'!$A$1:$V$1). Press Enter.
Right-click E2, select Format Cells, choose Percentage, and set Decimal places to 0.
Drag the fill handle from E2 down alongside the remaining student rows. The student-answer row changes while the answer-key range stays fixed.

Calculate student scores in WPS Spreadsheets
Use the same SUMPRODUCT formula in WPS Spreadsheets to compare each student's answers with a complete answer key and display the result as a percentage.
- 1. Open the workbook: Open the workbook in WPS Spreadsheets and check that the T1 ANS sheet contains the complete answer key in A1:V1.
- 2. Enter the formula: Select E2 and enter =SUMPRODUCT((F2:AA2='T1 ANS'!$A$1:$V$1)*1)/COUNTA('T1 ANS'!$A$1:$V$1), then press Enter.
- 3. Display a percentage: Right-click E2, open Format Cells, select Percentage, and set the decimal places to 0.
- 4. Apply it to other rows: Drag E2's fill handle down to calculate scores for the other students.

Frequently Asked Questions
What happens if a student leaves an answer blank?
If the corresponding answer-key cell is filled, the blank student answer is counted as incorrect.
Can I use this formula if the answer key has blank cells?
Fill every cell in 'T1 ANS'!A1:V1 before using this formula. A blank student cell can match a blank key cell and inflate the score.
Why does the answer-key range use dollar signs?
The dollar signs in $A$1:$V$1 keep the answer-key range fixed when you drag the formula from E2 down to other student rows.
Does this formula work for questions with different point values?
No. It gives every question equal weight. Differently weighted questions require a formula that multiplies each match by its assigned point value.




