In Excel how to sum only the top x values in a series?

[DennisM]

In our orienteering sports club we record the points scored by each
competitor at each event throughout the year. However, to balance out the
competition a little, we only tally the top (say) 7 scores out of (say) 12
events. A reliable formula to do this would save hours of manual adjustments.

 

[JulieD]

Hi Dennis

here's a reply from Chip Pearson to a similar question a while ago

Use the following array formula:

=SUM(LARGE(A1:A12,ROW(INDIRECT("1:7"))))

Change A1:A7 to the appropriate range. Since this is an array
formula, you must press Ctrl+Shift+Enter rather than just Enter when
you enter the formula and whenever you edit it later. If you do this
properly, Excel will enclose the formula in curly braces {}.

This will sum the largest 7 scores, edit the 1:7 bit to change it to the
number of scores you want to include.

Cheers
JulieD

 

[Frank Kabel]

Hi
one way:
=SUM(LARGE(A1:A20,{1,2,3,4,5,6,7}))