Grading Multiple Choice Exam

[Jay Windley]

I am setting up a spreadsheet for my students' answers to a multiple choice
test. One of the things I want to do is see how many students got each
question correct, and that is easy enough to do using COUNTIF. However, I am
having a hard time creating a function that will tell me how many questions a
student got correct.

Suppose the key is in row 1, columns B through K (so B1:K1), and below that
I have a row for each student (with student names in column A). So the first
student has his name in A2, and his answers in B2:K2. So how do I count the
number of cells in B2:K2 which match the corresponding cell in B1:K1? I'm
aware that I could do something hideous like
=IF(B2=B1,1)+IF(C2=C1,1)+...+IF(K2=K1,1)
but there has got to be a more elegant way! I know that I could also create
another column for each student that compares their responses to the key row,
but it seems like there should be a function to do this.

Thanks!

 

[Bernard Liengme]

Use =SUMPRODUCT(--($B$1:$K$1=B2:K2))
The $s allow you to copy down the column.
The double negation converts Boolean (True/False) to numbers (1/0) so
summing can occur.
best wishes (I'm glad I'm retired and exam marking is history for me!)

 

[Jay Windley]

Thank you so much for the quick reply, that worked fantastic!

Jay