Select the Top 20 within a formula

[Bryan Hessey]

How can I select the top 20 within a formula so that I can
=sum( ~~ select top 20 from a range I$56:I100~~ )

 

[ScottO]

Try this ...

=SUMIF(I$56:I100,">"&LARGE(I$56:I100,21))

Rgds,
ScottO

"Bryan Hessey" <[email protected]>
wrote in message
|
| How can I select the top 20 within a formula so that I can
| =sum( ~~ select top 20 from a range I$56:I100~~ )
|
|
| --
| Bryan Hessey
| ------------------------------------------------------------------------
| Bryan Hessey's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=21059
| View this thread: http://www.excelforum.com/showthread.php?threadid=385482
|

 

[Max]

One way ..

Try: =SUMPRODUCT(--(LARGE($I$56:$I$100,ROW(A1:A20))))

 

[BenjieLop]

How can I select the top 20 within a formula so that I can
=sum( ~~ select top 20 from a range I$56:I100~~ )

Check this out and see if this is what you need ...

=SUMPRODUCT(LARGE($I$56:$I$100,ROW(1:20)))

Regards.

 

[Bryan Hessey]

Worked wonderfully, many thanks

Brya

 

[Bryan Hessey]

Scott,
the
=SUMIF(I$56:I100,">"&LARGE(I$56:I100,21))
doesn't work where there are two rows of the same value about th
'20' mark.

Max,
with the: =SUMPRODUCT(--(LARGE($I$56:$I$100,ROW(A1:A20)))) the $10
needs to be not $ (as $I100), and the A1:A20 need to be $1:$20,
otherwise ok

With BenjieLop's original answer I adjusted the $I$100 and the 1:20 t
$1100 and $1:$20 and it formula dragged well.

Thanks again peopl

 

[Max]

Glad to hear you got it to work,
and knew how to adapt the references to suit ..