Select the Top 20 within a formula

B

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~~ )
 
S

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
|
 
B

BenjieLop

Bryan said:
How can I select the top 20 within a formula so that I can
=sum( ~~ select top 20 from a range I$56:I100~~ )
Click to expand...

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

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

Regards.
 
B

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
 
Top