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

Try this ...

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

Rgds,
ScottO

"Bryan Hessey"
wrote in message
news:Bryan.Hessey.1ru1qb_1120795506.9867@excelforu m-nospam.com...
|
| 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
|

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 <atyahoo<dotcom
----
"Bryan Hessey"
wrote in message
news:Bryan.Hessey.1ru1qb_1120795506.9867@excelforu m-nospam.com...

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 Wrote:
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.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=385482

Worked wonderfully, many thanks

Bryan


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

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


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

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 <atyahoo<dotcom
----