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

 

[Richard Schollar]

I'm not 100% certain that that is how an Excel C++ programmer would explain it, but it's how I understand it. It seems to hold true in the odd test I've done eg:

=SUMPRODUCT(MATCH(11,C:C,0))

will return 65536 if 11 is in C65536 for example.

 

[Aladin Akyurek]

I'm not 100% certain that that is how an Excel C++ programmer would explain it, but it's how I understand it. It seems to hold true in the odd test I've done eg:

=SUMPRODUCT(MATCH(11,C:C,0))

will return 65536 if 11 is in C65536 for example.

SUM simply operates on the result of LARGE, a notion well eastablished in maths and in programming languages. I expect that

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

would also succeed.

 

[GorD]

Would it be easy to expand this to include another criteria in the sumproduct. - i.e sum the three largest entries from a list of competitors but only for a particular team.

In my example there are many teams and there can be varying amounts of people representing a team. The three highest scores go forward as the team score - I want to work out all te team scores.

As I haven't been about the board much recently my excel as gone a bit rusty

 

[xld]

=SUM(LARGE(IF(A1:A15="a",B1:B15),{1,2,3}))

another array formula

 

[GorD]

Thanks that was exactly what I was tring to come up with ( unsucessfully)