I have a list of teams with a list of players next to it

(Would it be better/easier if the players are in a single cell?)

+Team Name+--------+--------+
|  Team 1 |Player 1|Player 2|
|  Team 2 |Player 3|Player 4|
|  Team 3 |Player 1|Player 3|
|  Team 4 |Player 2|Player 4|

Then I have a list of games with what team played against what team and who won

|Game 1|Team 1|Team 2|Team 1|
|Game 2|Team 3|Team 4|Team 4|

I'd like count how many times a specific player has won and how many times he has played

|Player 1|   1 |    2 |
|Player 2|   2 |    2 |
|Player 3|   0 |    2 |
|Player 4|   2 |    2 |
        List of teams   Specific player
                 v       v

Using =COUNTIF(H2:U999; A2) does not work if a team composition is reoccurring

I don't know if it's matters but it's a online google spreadsheet

  • In last screen shot you have list of Players who played and Wins and your Question is, "count how many times a specific player has won and how many times he has played" !! – Rajesh S Jan 28 '18 at 9:57
  • Better Post the Link of Google Sheet. – Rajesh S Jan 28 '18 at 9:57

Maybe you want it like this:


Hope this helps

  • 1
    While including a link to a demo file is helpful the solution should be described in the answer itself because the linked file could be lost. – Rubén Jan 28 '18 at 17:11
  • It helps yes, but is there a way to not have 6 players next to the games tab? For example if you'd play a game with 6, 8 or 10 players that tab would be massive – Wanjia Jan 28 '18 at 18:17
  • Yes, just insert new team and player in TEAM table and add new game, and then copy the formula down at GAMES and PLAYER tables. Don't forget to check and modify the reference range for VLOOKUP and COUNTIF . I just made some modification in the example table (in Google Drive) to show what I mean. Check it out. – Sofyan Thayf Jan 29 '18 at 0:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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