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

[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

Bryan

[ScottO]

Try this ...

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

Rgds,
ScottO

"Bryan Hessey" <[email protected]>
wrote in message
news:[email protected]...
|
| 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...o&userid=21059
| View this thread: http://www.excelforum.com/showthread...hreadid=385482
|

[Max]

One way ..

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Bryan Hessey" <[email protected]>
wrote in message
news:[email protected]...
>
> 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...o&userid=21059
> View this thread: http://www.excelforum.com/showthread...hreadid=385482
>

[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 the '20' mark.

Max,
with the: =SUMPRODUCT(--(LARGE($I$56:$I$100,ROW(A1:A20)))) the $100 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 to $1100 and $1:$20 and it formula dragged well.

Thanks again people

[Max]

Glad to hear you got it to work,
and knew how to adapt the references to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----