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))))

[#11]

@Sergei Baklan WOW, thank you both for posting those spreadsheets for golfing scoring and identifying skins.  I’ve been looking for something like this to use at our club (nine hole golf club) for awhile now and I just arrive here my searching the internet.  I would like to know a little bit more about the different tabs in the sample spreadsheet and how they are used.  Can someone explain the use of the tabs and where you would enter scoring?  I’m looking specifically to use this at our league nights.  We often play a 2 person scramble so I would need to add a second column for a 2nd name and possibly even a 3rd and 4th column because we sometimes play a 3 and 4 person scramble.  I’m hoping by adding additional name columns it won’t change any of the formulas going on in the spreadsheet.  It would be nice as well if I could run some sort of macro that would automatically sort from the lowest total score shot to the highest score and keep all the other data in the same row together (names, scores on each holes and totals all stay together from lowest to highest score shot) and places them in ascending order.  As an added bonus it would be really nice if the macro could also take into consideration progression scoring from the last hole #9 to hole #1 (in our case) when ties on holes are found with the same total score when sorting from lowest to highest score.  To explain this, if three teams finish with the same score, the macro would look first at hole #9 and look for the lowest score.  If two teams score a 3 and one a 4, the 4 would make that team be in 3rd place.  The other two teams that are tied on hole 9, would go back to hole #8 to see what both their scores were on that hole….. you continue working your way back towards hole #1 until one team has score a lower score on a certain hole to determine who finished in first place.  Is there a way to accomplish this with this sample spreadsheet?  You can email me at email address removed for privacy reasons directly if you have any questions.  THANKS.