Return Name of Category with Max Sum

[gamerosko]

Hi guys,
can you help with formula? Below is a list of Customer transactions & their values within Industries. Formula should Sum Up Prices in Industries for each Cust ID and return name of Max Sum Industry into Leading Industry.

Cust ID	Industry	Price	Leading Industry
5	Restaurants	100
1	Airlines	2300
3	Airlines	3230
5	Lodging	800
2	Telco	50
1	Telco	120
5	Restaurants	750

<tbody>
</tbody>

Thus for Cust ID 5 rows this should be Restaurants, for Cust ID 1 Airlines etc... If there are more Leading Industries with same values, return any of the leading one.

Thanks much for help. BR, Gamca

 

[JayBurn]

Simplest way would be to create a totals table;

Restaurants	=sumif([Industry],[Ref],[Price]	=RANK([SUMIF],[SUMIF RANGE],0)
Airlines
Lodging
Telco

<tbody>
</tbody>

Then you can either INDEX MATCH or VLOOKUP the Rank into Leading Industry?

 

[Eric W]

In D2:

=INDEX($B$2:$B$8,MATCH(A2&"|"&MAX(IF($A$2:$A$8=A2,$C$2:$C$8)),$A$2:$A$8&"|"&$C$2:$C$8,0))
confirmed with Control+Shift+Enter.

Also, I got Lodging for Cust ID 5 (800). Let me know if this works for you.

 

[gamerosko]

In D2:

=INDEX($B$2:$B$8,MATCH(A2&"|"&MAX(IF($A$2:$A$8=A2,$C$2:$C$8)),$A$2:$A$8&"|"&$C$2:$C$8,0))
confirmed with Control+Shift+Enter.

Also, I got Lodging for Cust ID 5 (800). Let me know if this works for you.

Hello Eric, thanks for help - actually Cust ID 5 showing Lodging is a mistake - it should show Restaurants (850 in totals for Cust 5) - so that is the only difference.. I am not looking for max line, but for max subtotal.

 

[wideboydixon]

I couldn't see how to do it without using a helper column. Someone else may have more luck:

Book1
	A	B	C	D	E
1	Customer ID	Industry	Price	Leading Industry	Helper
2	5	Restaurants	100	Restaurants	850
3	1	Airlines	2300	Airlines	2300
4	3	Airlines	3230	Airlines	3230
5	5	Lodging	800	Restaurants	800
6	2	Telco	50	Telco	50
7	1	Telco	120	Airlines	120
8	5	Restaurants	750	Restaurants	850
Sheet2

Cell Formulas
Range	Formula
E2	=SUMIFS($C$2:$C$8,$A$2:$A$8,$A2,$B$2:$B$8,$B2)
D2	{=INDEX($B$2:$B$8,MATCH(MAX(IF($A$2:$A$8=$A2,$E$2:$E$8)),$E$2:$E$8,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[Eric W]

Sorry, I missed the subtotal part. We can still do it with a single formula, similar in principle to what WBD came up with:

Excel 2012

	A	B	C	D
1	Cust ID	Industry	Price	Leading Industry
2	5	Restaurants	100	Restaurants
3	1	Airlines	2300	Airlines
4	3	Airlines	3230	Airlines
5	5	Lodging	800	Restaurants
6	2	Telco	50	Telco
7	1	Telco	120	Airlines
8	5	Restaurants	750	Restaurants

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula D2 {=INDEX($B$2:$B$8,MATCH(MAX(SUMIFS($C$2:$C$8,$A$2:$A$8,A2,$B$2:$B$8,$B$2:$B$8)),SUMIFS($C$2:$C$8,$A$2:$A$8,A2,$B$2:$B$8,$B$2:$B$8),0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[gamerosko]

Thanks Eric - working nicely.. I thought it will be something nasty like this.. !
Thanks as well to others - nice ideas !

BR, gamerosko

 

[Eric W]

Nasty? I expected it to be MUCH longer!

Glad we could help!