Sum points per row based on if expected value is unique within row

Question

Given a Google spreadsheet of quiz answers (with rows for questions, and columns for people's answers), I want to work out a person's total score. The scoring system is that if only one person gives the correct answer, that person scores 2 points; otherwise all people giving the correct answer get 1 point.

An example table might look like the below:

+----------+-------+-------+---------+---------------+-----------------------------------+
| Question | Alice |  Bob  | Charlie | Actual answer |              (Notes)              |
+----------+-------+-------+---------+---------------+-----------------------------------+
| Q1       | seven | eight | eight   | seven         | (Alice scores 2)                  |
| Q2       | tree  | tree  | tree    | tree          | (Everyone scores 1)               |
| Q3       | one   | seven | two     | three         | (Everyone scores 0; note 'seven') |
+----------+-------+-------+---------+---------------+-----------------------------------+

The totals row would be:

| Totals   | 3    | 1      | 1       |

I can get close using a formula like =ArrayFormula(SUMPRODUCT(IF(COUNTIF($B2:$D4,$E2:$E4)=1,2,1),COUNTIF(B2:B4,$E2:$E4))) (in Alice's column), but I need the first COUNTIF to act per row, rather than on the entire 2D array. At the moment, it counts the 'seven' in Bob's Q3 answer, and so only awards Alice 1 point for Q1.

In practice, I've created a new column for 'Points awarded' which counts the number of correct answers per row and gives either 1 or 2, allowing me to work around the issue, but I'm interested to know if there's a way of doing it in a single cell.

Answer 1

Let's assume the header "Question" in the OP is in cell A1 and that the string "Total" is in cell A5.

Given the sample data in the OP, placing the array formula into B5 will do the trick:

=ArrayFormula(TRANSPOSE(MMULT(TRANSPOSE(QUERY({(B2:D4=E2:E4)*1})*IF(IFERROR(MMULT(QUERY({(B2:D4=E2:E4)*1}),TRANSPOSE(COLUMN(B1:D1))^0))=1,2,1)),ROW(A2:A4)^0)))

For any data set, replace:

B2:B4 with the range containing the answers given by individuals

E2:E4 with the range containing the correct answers

B1:D1 with the header row containing the respondents' names

A2:A4 with the range containing the question labels (in this case, "Q1", "Q2", "Q3")

Also, where the IF clause ends in "=1, 2, 1" the English translation is "IF (the total number correct) =1, then multiply the correct response by 2 points; otherwise, multiply any correct responses by 1 point. Adjust to fit individual needs.

Answer 2

An inelegant way to calculate this is a single cell would be to chain if statements together by saying for each person's question/answer:

if person is right & others are all wrong give 2

else if person is right give 1

else give 0

eg:

=if(AND(E2=B2,OR(E2<>C2,E2<>D2)),2,if(E2=B2,1,0)) + 

if(AND(E3=B3,OR(E3<>C3,E3<>D3)),2,if(E3=B3,1,0)) + 

if(AND(E4=B4,OR(E4<>C4,E4<>D4)),2,if(E4=B4,1,0))

But I think your current approach is better, using a subtotal field will allow you to debug everything much more easily. You can always hive off your intermediate step fields into another tab if you don't want pollute the pure question & answer data.

Incidentally I was doing something similar but I wanted a league table at the end so I created an intermediate table of the unsorted people/scores referencing the main table. Then used data | pivot table to create a new tab containing the sorted people/scores - but as the format was a bit rubbish I referenced the pivot table cells from the original tab to create a nicely formatted table with people in the right order on the original tab.