sum largest 1:n values within a range. Using a specified cell to define n.

[aki4020]

Hi there,




I am trying to sum the largest n values in a range and I would like to specify the n by using a cell.




What I am trying to achieve is something like this;



=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:20"))))


but instead of taking the sum of the 20 largest values I would like to sum the number of values specified by a given cell such as $A$1


I have tried to find solutions but haven't yet been quite successful.


Any help is greatly appreciated,


Thanks in advance,

[JeteMc]

Welcome to Excel Forum.
Try the following:

Please Login or Register  to view this content.

Let us know if you have any questions.

[newdoverman]

Here is an other way. It is a little shorter but is an array formula. With the range of values in A1:A20 and the variable number in M1 enter this where you want the answer with Ctrl+Shift+Enter. (Array formula)

Please Login or Register  to view this content.

[Tony Valko]

If I understand what you want to do...

=SUMPRODUCT(--(Rng>=LARGE(Rng,A1)),Rng)