How to count top 10 values?

[Handyy]

Hi,

I couldn't find the right formula for this... I'd like to sum top 10 values from a range of values in cells. Currently only way I can think of is to sort all the values first and then count the top 10 cells. But is there a way to find the top values with a automatic formula, which would fit to one cell?

Example:

A
4
6
8
3
13
4
16
11
9
2
8
8
10
5

Sum from top 10 values would be 96... But how to reach this with a formula?

[shg]

=sumif(a1:a14, ">=" & large(a1:a14, 10))

EDIT: That's in error if there are multiple values at Large(..., 10)

[NBVC]

I get 94 with:

=SUMPRODUCT(--(A1:A14>=LARGE(A1:A14,10)),A1:A14)

[DonkeyOte]

On the off-chance you happen to have multiple instances of the 10th largest value you may need to use something along the lines of the below if only 1 of said value is to be included:

=SUMPRODUCT(LARGE(A1:A14,ROW(INDIRECT("1:10"))))

Note: the use of INDIRECT makes the above Volatile.

[contaminated]

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

Confirm with ctrl+shift+enter

[shg]

=sum(large(a1:a14, {1,2,3,4,5,6,7,8,9,10}))

Confirm with regular old Enter.

[contaminated]

Hm,
in point of fact alomost all formulas are the same...

[DonkeyOte]

Not really -- yours is a CSE Array, mine is not a CSE Array but is processed like an Array (and is Volatile) whereas shg's is the more efficient being neither an Array nor Volatile.

[shg]

Also, CWE, your formula changes if you insert rows anywhere above row 10, and NBVC's formula has the same problem as the first one I posted.

[NBVC]

And CWE's won't work in Excel 2003 or earlier do the whole column ref of A:A in a CSE formula.

[contaminated]

Sorry if I said smth that you became angry with.

I just wanted to say that in most cases there were used LARGE, SUM, and ROW functions. Thst's why I said that they are the same...

Only this.
.

[shg]

No one is angry at all ...

[Handyy]

Thanks for all the replies! shg's suggestion seems to be very simple and works great and yeah, the result should be 94 instead of 96 like I said earlier...

[shg]

Great. Would you please mark the thread as Solved?