Function Problems

Compare Student Answers and Calculate Scores in Excel

Bushra ParveenBushra Parveen Nov 18, 2026 870 views

Question details

Calculate each student's percentage score by comparing a row of answers with an answer key on another sheet.

Compare Student Answers and Calculate Scores in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the scoring formula

Select E2 and enter =SUMPRODUCT((F2:AA2='T1 ANS'!$A$1:$V$1)*1)/COUNTA('T1 ANS'!$A$1:$V$1). Press Enter.

2
Format the score

Right-click E2, select Format Cells, choose Percentage, and set Decimal places to 0.

3
Score the other students

Drag the fill handle from E2 down alongside the remaining student rows. The student-answer row changes while the answer-key range stays fixed.

Use SUMPRODUCT to calculate the percentage score
Keep the answer key complete: A blank student answer counts as incorrect when its answer-key cell is filled. Blank cells in the answer key can produce an incorrect score if the corresponding student cells are also blank.
Score answers in WPS Spreadsheets

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. 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. 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. 3. Display a percentage: Right-click E2, open Format Cells, select Percentage, and set the decimal places to 0.
  4. 4. Apply it to other rows: Drag E2's fill handle down to calculate scores for the other students.
Score 22 equally weighted answers with one formulaCopy the formula down for multiple studentsOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formatting controls
microsoft office alternative - wps office

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.