Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

In a spreadsheet in Google Spreadsheets, I have multiple-choice answers I would like to grade. One row (user 0) contains the answer key, and the other rows contain user answers.

User  Q1   Q2   Q3   Q4   Q5   Score
0     A    C    D    C    B
1     A    C    B    C    B    [need formula here]
2     B    A    C    D    B    [need formula here]

I'm trying to come up with a spreadsheet formula that would allow me to automatically calculate how many answers for a given user match up with the answer key, but I haven't had much luck.

Any suggestions?

share|improve this question

1 Answer 1

There's a function on Google Spreadsheets called ARRAYFORMULA. Here's how to use it in your case:

=ARRAYFORMULA(SUM(IF(EXACT($B$2:$F$2,B3:F3),1,0)))

This saves from the repetition of typing out:

=SUM(IF(EXACT($B$2,B3),1,0), IF(EXACT($C$2,C3),1,0), IF(EXACT($D$2,D3),1,0), etc...)

Assuming your spreadsheet was setup like this:

   A     B    C    D    E    F    G
1  User  Q1   Q2   Q3   Q4   Q5   Score
2  0     A    C    D    C    B    key
3  1     A    C    B    C    B    4
4  2     B    A    C    D    B    1

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.