Finding best selling item by sum of sales, for each salesperson

[ksscott]

I am working in Excel 365 and have the following problem: Let's say my data is as follows:

	A	B	C
1	Salesperson	Product Sold	Value
2	Bob	Shoes	56​
3	Bill	Shoes	41​
4	Jack	Socks	47​
5	Bob	Socks	34​
6	Bob	Pants	61​
7	Jack	Shoes	68​
8	Bill	Pants	52​
9	Jack	Socks	85​
10	Bill	Socks	64​
11	Jack	Socks	37​
12	Bill	Pants	55​
13	Bob	Pants	13​

Now, for a unique list of salespeople, I'd like to get their best selling product, by sum of Value.
For Bob, this would be Pants. I tried doing SORTBY by sum of Value but it doesn't accept the sum.

=SORTBY(UNIQUE(FILTER(B:B,$A:$A="Bob")),SUM($C:$C),-1)

I have data from hundreds of salespeople and hundreds of products so it's not practical to do SUMIFS etc. Thank you for any help.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LET(u,UNIQUE(FILTER(B:B,$A:$A="Bob")),TAKE(SORTBY(u,SUMIFS($C:$C,A:A,"Bob",B:B,u),-1),1))

 

[Fluff]

Another option for all the names

Fluff.xlsm
	A	B	C	D	E	F
1	Salesperson	Product Sold	Value
2	Bob	Shoes	56		Bob	Pants
3	Bill	Shoes	41		Bill	Pants
4	Jack	Socks	47		Jack	Socks
5	Bob	Socks	34
6	Bob	Pants	61
7	Jack	Shoes	68
8	Bill	Pants	52
9	Jack	Socks	85
10	Bill	Socks	64
11	Jack	Socks	37
12	Bill	Pants	55
13	Bob	Pants	13
14
View

Cell Formulas
Range	Formula
E2:F4	E2	=LET(u,UNIQUE(FILTER(A2:A100,A2:A100<>"")),HSTACK(u,MAP(u,LAMBDA(x,LET(mu,UNIQUE(FILTER(B2:B100,A2:A100=x)),TAKE(SORTBY(mu,SUMIFS($C:$C,A:A,x,B:B,mu),-1),1))))))
Dynamic array formulas.

 

[ksscott]

Thank you very much. I get a #NAME? Error with both of those solutions unfortunately. Maybe my Excel doesn't have the features that would allow that function.

 

[Fluff]

Ok, does this work for you

=LET(u,UNIQUE(FILTER(B:B,$A:$A="Bob")),INDEX(SORTBY(u,SUMIFS($C:$C,A:A,"Bob",B:B,u),-1),1))

 

[ksscott]

That works, thank you!
I tried for hours to do this today. Thank you again.

 

[Fluff]

You're welcome & thanks for the feedback.