One non-array formulas play ..

Assuming the sample table is in A1:B15,

Put in D2:
=IF(ISNUMBER(SEARCH("Subtotal",A2)),B2-ROW()/10^10,"")
(Leave D1 empty)

Put in E2:
=IF(ISERROR(LARGE($D:$D,ROWS($A$1:A1))),"",INDEX(A :A,MATCH(LARGE($D:$D,ROWS(
$A$1:A1)),$D:$D,0)))

Copy E2 across to F2

Select D2:F2, fill down to D15

Cols E & F will return the full descending sort of
the rows with "Subtotal", viz.:

Subtotal Yellow 15
Subtotal Black 11
Subtotal Blue 10
Subtotal Red 3
Green Subtotal 2
(blank rows below)

And should there be any tied amounts in the "Subtotal" rows,
these will appear within cols E & F in the same relative order
that they are within cols A & B.
(there's an implicit tie-breaker built into the criteria formula in col D)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"hindsight" wrote
in message ...

I have a list of the following accounts:

Account Amount
Red 1
Red 2
Subtotal Red 3
Blue 10
Subtotal Blue 10
Black 3
Black 1
Black 8
Subtotal Black 11
Yellow 15
Subtotal Yellow 15
Green 1
Green 1
Green Subtotal 2

I want to return the 3 highest *subtotal* values. If I just use LARGE
then I'd get 15, 15, 11.

Do I have to use an array?

Any thoughts?

JB


--
hindsight
------------------------------------------------------------------------
hindsight's Profile:
http://www.excelforum.com/member.php...fo&userid=6360
View this thread: http://www.excelforum.com/showthread...hreadid=392396