I have a worksheet that is used to score monthly target shooting for a club. Months are the columns and members names are the rows. They want to sum the top 8 scores for each member and use that to select the top 3 shooters for the year. The big problem is that not all contestants show up each month, so some of the guys don't have 8 months of scores. I tried this formula:
=SUMPRODUCT(LARGE(C7:N7,{1,2,3,4,5,6,7,8}))
which works great IF there are 8 scores in the range. If not, I get the dreaded #NUM error message.
I then thought that using an IF statement and COUNTIF to determine if there were 8 scores across all months or not but then what? If there are 8, that settles the TRUE portion of the formula and if there are only 7 scores I could set up the ELSE part of the formula to evaluate 7, but what if there aren't even 7 months with scores? It seems like the SUMPRODUCT function won't work for this...
Does anyone have any ideas about how to determine how many scores there are in each row for the year, sum the top 8 scores if they have them, or sum less if they didn't make it to all the shoots?
Am I just overthinking this?
Thanks guys!
Mike
Bookmarks