Excel Formula Curiosity

[Expunge]

I just used the formula =SUM(LARGE(C4:C16,{1,2}))

just curious if i wanted to sum more than the top 2 values if there is a much better way of doing this. say the top 1000 values. Potential ties exist at the cutoff point exist.

[NoName]

Making no claim that this is the most efficient way...

=SUMIF($D$17:$D$1141,">"&LARGE($D$17:$D$1141,1000) ,$D$17:$D$1141)+LARGE($D$17:$D$1141,1000)*(1000-COUNTIF($D$17:$D$1141,">"&LARGE($D$17:$D$1141,1000 )))

[Expunge]

ah found something similar to mine....

entered as an array formula (ctrl shift enter)

=sum(large(a1:a10000,row(indirect("1:1000"))))

[KRWarner]

I have seen some creative use of sumproduct in here. Here's another way to go about it getting the result.

=SUM(A1:A10000)-SUMPRODUCT(A1:A10000,--(A1:A10000<LARGE(A1:A10000,1000)))

I take it you want to add everything greater and everything equal to the 1000 Largest. I summed everything and subtracted everything smaller.

[KRWarner]

Originally Posted by KRWarner

I have seen some creative use of sumproduct in here. Here's another way to go about it getting the result.

=SUM(A1:A10000)-SUMPRODUCT(A1:A10000,--(A1:A10000<LARGE(A1:A10000,1000)))

I take it you want to add everything greater and everything equal to the 1000 Largest. I summed everything and subtracted everything smaller.

Stupid me.

=SUMPRODUCT(A1:A10000,--(A1:A10000>=LARGE(A1:A10000,1000)))

Just use the Greater than or equal to, I guess I was over thinking it.

[Expunge]

Originally Posted by KRWarner

I take it you want to add everything greater and everything equal to the 1000 Largest. I summed everything and subtracted everything smaller.

Not really i'm looking to add up 1000 numbers, if the is a 5 way tie at the 1000th number and i have already added 998 numbers i want 2 of the 5 tie.

[Old Actuary]

Here's my solution:

=SUMIF($A$1.$A$10000, ">"&N(LARGE($A$1.$A$10000,1000)))
+(1000-COUNTIF($A$1.$A$10000, ">"&N(LARGE($A$1.$A$10000,1000))))
*LARGE($A$1.$A$10000,1000)