SUM Top x Large Values

[Jon von der Heyden]

Hi,

I have a table of numbers (sales), and I am able to add up the top say 10 sales like so:

=SUM(LARGE(A:A,{1;2;3;4;5;6;7;8;9;10}))

How would I do the same for say the largest 100 values. I don't want to create an array constant {1;2......;100}. Using RANK doesn't either deliver desired results because some sales are for the same amount so adding top 100 ranking sales will include more than 100 sales (hope that makes sense).

I can achieve with a pivot table to provide a list of top 100 but I am looking for formula solution if possible.

I appreciate any help given.

Jon

 

[barry houdini]

Try

=SUM(LARGE(A:A,ROW(INDIRECT("1:100"))))

confirmed with CTRL+SHIFT+ENTER

 

[Jon von der Heyden]

Brilliant solution Barry, as always.

I was unsure about the use of INDIRECT and I seem the get the same result without the INDIRECT. I'm just being curious, why have you chosen INDIRECT?

Many thanks, much appreciated.
Jon

 

[Richard Schollar]

Try inserting a few rows whilst you have A1 selected...

 

[Jon von der Heyden]

Ah very clever!!! I knew there would be a good reason for it!

One more question if I may please. I thought array formula would not allow for whole columns (A:A), yet this works perfectly. What is the rule around use of whole columns in array formula?

 

[Aladin Akyurek]

Sum Exactly N Largest.xls
	A	B	C	D	E	F	G	H
1
2					Sum Exactly N Largest
3					N	Nth Value	With INDIRECT	No INDIRECT
4	20				4	20	102	102
5	25
6	32
7	25
8	17
9	20
10
11
12
Sheet1

With INDIRECT...

G4:

=SUMPRODUCT(LARGE(A:A,ROW(INDIRECT("1:"&E4))))

Without INDIRECT (almost twice faster)...

F4:

=LARGE(A:A,E4)

H4:

=SUMIF(A:A,">"&F4)+(E4-COUNTIF(A:A,">"&F4))*F4

 

[Jon von der Heyden]

Nice! Makes good sense, thanks Aladin.

Still curious about use of whole colums though.

Thanks all for the inputs. Problem is solved!

Jon

 

[Richard Schollar]

The array isn't working on the whole column - it is working on the result of the LARGE function...

 

[Jon von der Heyden]

I still don't geddit, being thick...

Is it not evaluating the entire whole column A:A and sourcing the top 100 values, and as such is working on the whole column?

 

[Jon von der Heyden]

Ah, now I see, The SUM is only working with the top 100 values retrieved and as such not using the whole column.

Sorry, I geddit now! Thanks Rich...