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.

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Cust ID[/TD]
[TD]Industry[/TD]
[TD]Price[/TD]
[TD]Leading Industry[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Restaurants[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Airlines[/TD]
[TD]2300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Airlines[/TD]
[TD]3230[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Lodging[/TD]
[TD]800[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Telco[/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Telco[/TD]
[TD]120[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Restaurants[/TD]
[TD]750[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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;
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Restaurants
[/TD]
[TD]=sumif([Industry],[Ref],[Price]
[/TD]
[TD]=RANK([SUMIF],[SUMIF RANGE],0)
[/TD]
[/TR]
[TR]
[TD]Airlines
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lodging
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Telco
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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
Cust ID	Industry	Price	Leading Industry
Restaurants	Restaurants
Airlines	Airlines
Airlines	Airlines
Lodging	Restaurants
Telco	Telco
Telco	Airlines
Restaurants	Restaurants

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]100[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]2300[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]3230[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]800[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]50[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]120[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]750[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=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))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[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!