Top 5 in a List - Alterntive to Pivot Table

[Anthony]

Hi
I have a list of companies and the rent they pay in a building, and I
want a formula that will list the top 3 companies by the total rent
they pay, without using a pivot table or filters.

For Example, if i have a list like this in a worksheets (below) i want
to be able to list by using a formula that the top tenant is Company B
at 55,000 (after adding up the 5 separate entries), 2nd is Company A
at 50,000 and third at Company D at 45,000.

Company A 50,000
Company B 10,000
Company B 10,000
Company B 15,000
Company B 20,000
Company C 30,000
Company D 25,000
Company D 20,000
Company E 40,000
Company F 32,000
Company G 15,000
Company G 20,000

Is there are way to get this without using a pivot table, knowing that
the Company Name casn appear more than once and that each amount would
have to be summed up.

Anthony

I wa

 

[Bob Phillips]

Try this

D1: = A1
D2: =IF(ISERROR(MATCH(0,COUNTIF(D$11,$A$1:$A$20&""),0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH(0,COUNTIF(D$11,$A$1:$A$20&""),0)))

D2 is an array formula, so commit with Ctrl-Shift-Enterk, and copy down to
D20

E2: =SUMIF(A:A,D1,B:B)
copy down to E20

G1:
=IF(ISERROR(SMALL(IF($E$1:$E$20=LARGE($E$1:$E$20,{1,2,3}),ROW($A1:$A20),""),ROW($A1))),"",
INDEX($A$1:$A$20,SMALL(IF($E$1:$E$20=LARGE($E$1:$E$20,{1,2,3}),ROW($A1:$A20),""),ROW($A1))))

G1 is also an array formula, copy down to G3

G1: =SUMIF(A:A,G1,B:B)
copy down to H3

You can hide columns SD:E to make it cleaner

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)