Excel 2016 Formula - skins

[stevesteph1234]

I am working on a formula that will calculate a Skins game for golf.  The criteria is that players participating in the skins game will have a y (for participating in skins) in column c.  Each column is  calculated individually for lowest score in the column.  The lowest unrepeated score that has a y in column c wins the skin for that hole and that score is highlighted.  

The 2nd formula calculates the total number of skins won for the entire spreadsheet and puts that answer in column R3.

In the example attached Player3 column f8 is the winner with a low score of 2 and the score of 2 is not repeated in column f rows 6 to 11, this player is highlighted yellow as the winner.  Column H has no winner because players who are participating in the skin tie for the lowest score.

My formula falls apart when a player that does not have a y in column c (player is not participating in skins) has the lowest score (this player is highlighted yellow and should not be).  (Example: column G row 8 should be highlighted.  This is lowest score for a participating skin player).

My formula falls apart again when a player who is participating has the same lowest score as a player who is not participating.  In this case the player who is participating should be highlighted in yellow. (Example Column I row 8 should be highlighted yellow as the winner).

[#2]

My formular is in conditional formatting =AND(C6="y",F6=MIN($F$6:$F$15),COUNTIF($F$6:$F$15,F6)=1)

[#3]

Hi,

In your formula you find MIN for all cells in the columns, not only for the cells for which in column C is "y".

If you are on Office365 or 2019 you may use MINIFS as

=AND($C6="y",F6=MINIFS(F$6:F$15,$C$6:$C$15,"y"),COUNTIF(F$6:F$15,F6)=1)

(second sheet attached), if not that could be

=AND($C6="y",F6=AGGREGATE(15,6,1/($C$6:$C$15="y")/(F$6:F$15>0)*F$6:F$15,1),COUNTIF(F$6:F$15,F6)=1)

and it's not necessary to use separate rule for each column, you may apply one rule for entire range. 

[#4]

Thank you so much for your help Sergei.  I have one additional problem.  For Column I, Player 3 is participating in Skins (c8 is a y) and the score is 3.  Player 4 is not participating and also has a 3 in Column I row 9.  Player 3 should be the winner and be highlighted.  When 2 players have the lowest score and one player has a y in column c and the other player does not, the player with the y in column c is the winner.  How do I add that to the formula that you gave me?  

Thanks again for all of your help.

[#5]

Okay, so we shall count with the condition

=AND($C6="y",F6=MINIFS(F$6:F$15,$C$6:$C$15,"y"),COUNTIFS($C$6:$C$15,"y",F$6:F$15,F6)=1)

Updated file is attached

[#6]

Thank you so much Sergei for the excellent answer.  The formula is perfect and I appreciate all your hard work.

[#7]

The skins are now calculating correctly.  My last task is to count the total skins and place them in column P row 4.  For example, there are 3 skins highlighted as wins.  Can you help me with the formula to put the 3 wins in column P row 4.  

Thanks so much for your help.

[#8]

I'd do that with helper row - add formula as

=SUMPRODUCT(($C6:$C15="y")*(F6:F15=MINIFS(F$6:F$15,$C$6:$C$15,"y"))*(COUNTIFS($C$6:$C$15,"y",F$6:F$15,F6:F15)=1))

in that row, column by column, and sum to P3.

In attached above formula is in F5:N5

[#9]

My requirements for the skins game has changed once again.  I need to total the number of skins game won by each player.  I am attaching the Example spreadsheet.  Can you help me with the formula that would take each player and total their wins.  Player 1 has won no skins game so his #Won by Player Column ), Row 6 is 0.  Player 3 has won 3 games (as highlighted) his total in Column O Row 8 is 3.

[#10]

We may add to helper row (#5) calculation of player on which position won

=IFNA(MATCH(1,INDEX(($C6:$C15="y")*(F6:F15=MINIFS(F$6:F$15,$C$6:$C$15,"y"))*(COUNTIFS($C$6:$C$15,"y",F$6:F$15,F6:F15)=1),0),0),0)

after that calculate total wins for each player as

=SUMPRODUCT(--($F$5:$N$5=(ROW()-ROW($E$5))))