Formula to pick players on a fantasy golf team

**tahoeast** · 01-11-2013, 01:15 AM

Hi, I'm running a fantasy golf league and looking for a formula to pick golfers on each team that will be accruing points. Each team has 8 golfers. I have a table with each team organized from their best golfer down to their worst 1-8. Not all golfers play each week. Whether they are playing is in another column - formula works. The formula I'm looking for will choose players to accrue points based on this criteria:

Max of 3 golfers
Choose if manually picked (in another column)
Highest ranked golfers chosen first

You will see what I'm talking about in the attached spreadsheet. I'm not explaining it very well.

Thanks!

**tahoeast** · 01-11-2013, 01:53 PM

Have I stumped the community? Not possible. Please help. I tried to do this with Countifs, but it is "relational" I think and would require an array. I just don't get the array concept, please help.

**daddylonglegs** · 01-16-2013, 08:13 PM

Try this formula in G11 copied down

=IF(F11="x","x",IF(AND(E11="x",COUNTIFS(C$10:C10,C11,G$10:G10,"x")+COUNTIFS(C:C,C11,F:F,"x")<3),"x",""))

That works for your example but assumes that there will always be a maximum of 3 manual selections (is that the case - if not then what happens if there are more)? Also assumes that the team names are different for every set of 8 rows....is that the case?