Top 6 from 10, and sum

[Mark (MSA)]

Hi, hope someone can advise.

I have a spreadsheet that I use for my running club 'Grand Prix'
competition. Members run in 10 events during the season and they score
on there highest 6 places.

I have the events across the top, members names down the side, and I
fill in the points earned for each race. I have a SUM function at the
end which works fine at the moment because we have only done 3 races so
far. When we have completed 7 or more (up to 10) how can I get the SUM
function to just take the highest 6 numbers from the members total of
10 placings?

Hope that makes sense, and thanks in advance.

Mark

 

[Bob Phillips]

Mark,

This takes the top 6 (or 5 or 4 etc. if there are less)

=SUMPRODUCT(LARGE(C2:L2,ROW(INDIRECT("1:"&MIN(6,COUNT(C2:L2))))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Mark (MSA)]

Mark,

This takes the top 6 (or 5 or 4 etc. if there are less)

=SUMPRODUCT(LARGE(C2:L2,ROW(INDIRECT("1:"&MIN(6,COUNT(C2:L2))))))

Excellent, thanks Bob.