Google-sheets – Sum points per row based on if expected value is unique within row

google sheetsworksheet-function

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.

Best Answer

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.

Related Topic