LARGE Function with subtotals

[hindsight]

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

 

[Bryan Hessey]

you could use =LARGE(B1:B100,1)+LARGE(B1:B100,2)+LARGE(B1:B100,3) to get
the sum, or use each portion in it's own cell, ie
=LARGE(B1:B100,1)
=LARGE(B1:B100,2)
=LARGE(B1:B100,3)

 

[Max]

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)

 

[Domenic]

If your subtotals are the result of using the SUBTOTAL function, suc
as...

=SUBTOTAL(9,B2:B3)

...you can use the following formula...

D2, copied down:

=LARGE(IF(1-SUBTOTAL(3,OFFSET($B$2:$B$15,ROW($B$2:$B$15)-ROW($B$2),0,1))=1,$B$2:$B$15),ROWS($D$22))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!