Excel Formula to sum players with lowest winning score

[splawrie]

I am nearing the end of small project for a golf club and need some help.
In this spreadsheet teams\players are listed in column “E”.
The individual golf holes are listed in Columns F, G, H, I, J, K, L, M, N, P, Q, R, S, T, U, V, W, X
1. Teams\Players participating in the skins game are marked with a “y” in column “C”
2. The lowest participating team score excluding ties are highlighted in yellow using conditional formatting
The next step I need to complete is count the number of holes won by each team and total in column “Y”
Examples:
Hole 1 - In the attached spreadsheet Player3 won the first hole because they were participating and had the lowest score.
Hole 2 – Player5 and Player4 both had the lowest score but Player4 was not participating in the skins game so Player5 won the hole
Hole 3 – No player won the hole because although Player4 had the lowest score they were not participating in the skins game and the next lowest score ended in a tie with multiple teams
Hole 4 – Player won the hole because they had the lowest score of participating teams
Hole 5 – Player5 won the hole with the lowest score of a participating team
What I need to do next is count the number of wins for each team and total in column “Y”
In the previous examples, Player won once, Player3 won once, Player5 won twice.
Once I get the totals in column “Y” I will be able to total column “Y” and divide into total pot to get individual pay out for each team\player.

[XLent]

below is one option, albeit volatile, however there will be more efficient alternatives:

Please Login or Register  to view this content.

[soledad]

Array formula in [Y6]

Please Login or Register  to view this content.

Confirm with CTRL+SHIFT+ENTER
Hope this help