sum top 18

[jonty]

Hopefully this is a quick one for some one.
I have a list of 25 numbers and i would like the sum the top 18 numbers in one cell. Is there a formula something like sumif etc which cud do this please.

Thanks a lot

Jonty

 

[anno]

jonty
try =SUM(LARGE(A1:A50,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18})). adjust cell refs to suit.

 

[Jay Petrulis]

On 2002-04-01 19:28, jonty wrote:
Hopefully this is a quick one for some one.
I have a list of 25 numbers and i would like the sum the top 18 numbers in one cell. Is there a formula something like sumif etc which cud do this please.

Thanks a lot

Jonty

Hi Jonty,

WIth your data in A1:A25, try:

{ =SUM(LARGE(A1:A25,ROW(1:18))) }

This must be entered as an array formula with Ctrl+Shift+Enter rather than just Enter.

If array entered, Excel will place the braces {} around the formula you type.

You can also name a formula and place the above in the 'refers to' area. It does not have to be array entered if you use this method.

Regards,
Jay
This message was edited by Jay Petrulis on 2002-04-01 20:00

 

[jonty]

Thanks very much - both solutions are working brilliantly

Cheers
Jonty

 

[Dave Hawley]

Hi jonty


If you would like a more flexible solution just the the SUBTOTAL fumction on you data with Auto filters showing the top 18 (or whatever) This can be be found under Top Ten of the Auto filter.