Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Top 5 in a List - Alterntive to Pivot Table
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Top 5 in a List - Alterntive to Pivot Table
Try this
D1: = A1 D2: =IF(ISERROR(MATCH(0,COUNTIF(D$1:D1,$A$1:$A$20&""), 0)),"", INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(D$1:D1,$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) "Anthony" wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Feild List | Excel Discussion (Misc queries) | |||
Pivot Table List | Excel Discussion (Misc queries) | |||
pivot table field list | Excel Worksheet Functions | |||
pivot table list for web | Excel Discussion (Misc queries) | |||
Pivot table datarange as list? | Excel Discussion (Misc queries) |