Pool match results – counting player wins and losses in an array.

[youngbobbyf]

The pool Match consists of 7 games and the 4th game is always a doubles game. Teams must have at least 6 players so 2 players could play a doubles game as well as their singles game.
Please refer to the spreadsheet link. There are two tables, one for fixture and results details and the other for individual player performance. Player initials and game result is entered manually into the top table but there are formulae in the player performance table but I am struggling to create a formula to collate player wins and losses. I haven’t found anything similar online.
Player Jon Doe(“JD”) has 5 wins and 4 losses from 9 games. Player Rob McDuck has 4 wins and 3 losses from 7 games. I’ve entered these manually as an example of what I want it to do and I am trying to create a formula that will locate all player initials in the results table then test/count the cell to the right to see if it’s a “W” for a win or “L” for a loss. Can anyone help..?

https://1drv.ms/x/s!AijXUgi8s_dXjEGhi2UayCvwZKyP

 

[piotrekp1]

Isn't the solution here to just keep the canonical order of the initials, so that you have every match of JD (e.g.) in a row 4? Afterwards your question would have been easy and I don't see any shortcomings

 

[youngbobbyf]

That's exactly what I had in the spreadsheet for last season. Much easier with it being linear and if I can't find a solution for this I'll have to go back to it. I don't know for sure what I'm asking is possible but if there is a solution out there I hope someone will provide it. I thought something like =countif(offset((match("JD",A$4:P$11,0)),0,1,1,1),"W") would do it, but nope.

 

[youngbobbyf]

I seemed to have half solved it using this formula =COUNTIFS(B$4:P$11,"RM",OFFSET(B$4:P$11,0,1),"W"). The down side is that game 4 is a doubles game so I've got the two players initials in rows 7 and 8 with a merged cell to the right. The formula works fine for players in row 7 but not for the player in row 8. Can anyone suggest a solution for row 8 players...? I could unmerge the cells but then the match results would be wrong....

https://1drv.ms/x/s!AijXUgi8s_dXjEGhi2UayCvwZKyP

 

[youngbobbyf]

I solved it in the end by using this formula =COUNTIFS(C$4:AQ$11,A19,OFFSET(C$4:AQ$11,0,1),"W")+COUNTIFS(C$8:Q$8,A19,OFFSET(C$8:Q$8,-1,1),"W") where A19 points to a cell with the player initials in it. That formaulk obviously counts "W"ins. I simply replaced the "W" for "L" to count "L"osses.