Search code examples
google-sheetsformulas

Google Spreadsheet - compare cells on same row and get number of matches?

I have values in column A, B and C for Google Spreadsheet:

  • A contains the results of each match (1X2)
  • B is what person B guessed that match
  • C is what person C guessed

Players can for each match guess 1 (home win), x (equal), 2 (away win) or a combination of them, like x2.

How can I compare the player's guesses (column B and C) with the match result (column A) and get the number of correct answers/matches?

Example: Person B scores three points, because he guessed three matches correctly (not the second match though).

A       B        C
1       1x       x2
x       1        x
2       2        1x
x       1x       2

CORRECT: 3       1
Solution

  • Add 2 columns D and E, than represent either a 0 or 1, then sum columns D & E. You would then put: D2=IF(A2=B2,1,0) E2=IF(A2=C2,1,0) Then just copy paste formulas - since you aren't using absolute $ syntax for rows 3,4,5 etc. Then just have D1=SUM(D2:D99) E1=SUM(E2:E99) - or something to that effect depending on number of rows.