Golf Scores

Using old version of excel (97). Have 150 golfers playing (Column A), who are Flighted (Column B), playing 20 times in a season (Dates in Further Columns). On each of these dates, I will enter their net score. I will enter data during the year and at end of year sort by flight, but I want the computer to pick out the six best scores for each player and add them up so that the second sort will give me the best players total net score for the six games in order for a winner in each flight. I know how to do the sorting, but can't figure out a formula to have the six best scores for each person selected and added up in a column to sort. Can you help. Thx for your help.

John - Flight 1 - Scores 78, 88, 99, 76, 84, 90, 87, 85, 82, 85, 82, 78, 79, 90 ... Select six lowest scores and give me total of those six

This is the last place on the internet to complain about "excel spreadsheets for no reason" regardless of your feelings about golf though???

Anyway you should consider the function =RANK(range, position, 1) for the range of 20 scores across the 1st - 6th positions. Rank can be finicky about ties though so be aware of those workarounds. (You have to set the last term of Rank above 0 to swap it from descending to ascending order).

I was gonna say "oh just do this: best_six = SUMIF(range, >RANK(range,7,1), range) and you're good (well with error handling maybe?)" but... excel 97, huh?

Yes, I know, 97!! Kind of ages me, right? New computer has Works Spread Sheet. Am more familiar with Excel which I used for work (on old lap top, which I can spare for the project). Now retired and enjoying life to fullest!!

Sincere thanks for your reply. I will try it out and see how it goes.