sumif question

Hi rockville9,

Not only am I a former golf pro, I'm reasonably good with Excel. Could you post a copy of your spreadsheet with some example data and results. We can then come up with a formula that will do specifically what you need.

Thanks

You must zip Excel files before uploading them. Right-click on the file and choose Send To -> Compressed Folder. Upload the newly created file. (Or use WinZip or another zip utility to zip it and upload that file.)

Hi again, rockville,

Take a look at this updated version of your workbook. I made the following changes:

1. Created a new sheet called 'HandicapHoles'. Use this sheet to enter the handicap holes for each course (e.g. Hole 1 is the 5th hardest hole, Hole 2 is the 17th, etc.). I'm sure you're familiar with that information.

2. On the HandicapHoles sheet, I created a named range for each course (BLL = Barefoot Landing - Love, BLF = Barefoot Landing - Fazio, DC = Dunes Course, BLS = Barefoot Landing - Shark, HCPHoles = B2:S2). This is to make the formulas MUCH shorter, otherwise we'd be referencing something like IF(INDEX(HandicapHoles!$B$2:$S$2,MATCH(M$5,HandicapHoles!$B$1:$S$1,0)... instead of IF(INDEX(BLL,MATCH(M$5,HCPHoles,0)...

3. On your player tabs (numbered 1 through 16) I assumed the number stored in cell AF4 is the player's actual handicap. Hopefully that is correct, since the formulas I put into M7:AD7, M11:AD11, M15:AD15 and M19:AD19 reference it statically. I didn't take into account any rounding, e.g. 11.9 would be a 12 handicap vs. 11, though. It could be added, though, and would only slightly lengthen the formulas.

I updated the formulas for player 1 (tab "1") that are highlighted in yellow. They're not short (even with named ranges), but they get the job done. I also made the assumption that nobody would have a handicap higher than 36, since that is the highest recognized USGA handicap for men - which would result in 2 strokes per hole. Here is the formula for M7 (which I copied across to AD7, then copied that row to the other four courses in M11:AD11, etc.) You should be able to copy these rows to all of the other sheets as long as they are laid out the same.

Please Login or Register  to view this content.

Essentially the formulas in M7:AD7 (and the other Mxx:ADxx) check to see if the Handicap Hole value for that specific hole on that specific course is less than the golfer's handicap in AF4. If so, AND the handicap hole value is also less than AF4-18, then they get 2 strokes per hole. (If hole 1 was handicap hole 5, and the golfer had a 28 handicap then both conditions are true - the handicap hole is less than the handicap AND it's also less than the handicap minus 18.)

If one of those conditions fails, it performs a second test to see if the handicap hole is less than the golfer's handicap but not greater than the golfer's handicap minus 18. If that is the case (say the golfer's handicap is 11), then they get 1 stroke on any handicap hole of 11 or less.

If neither of those are true, then the only other option is that the handicap hole isn't less than the golfer's handicap, in which case the golfer doesn't get any strokes on that hole.

For you non-golfers that may seem confusing, and for golfers I may have made it confusing, but just know it works.