Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .