Return salesperson with maximum sales per client

[meamuka]

Hi, thanks for reading this post!

(1) I have a table with the data, like this:

Client	Salesperson	Sales ($)
Google	Mike	1,000
Apple	Jim	5,000
Apple	George	4,000
Apple	George	3,000
...	...	...

(2) On another sheet, I'm creating a report, like this:

Client	Frequent/Max Salesperson	Sales ($)
Google	Mike	1,000
Apple	George	7,000

I'm having a headache finding the formula for second column. I'm running 365 and I've tried the following: Nesting a MAXIFS with a SUMIFS, looking with a XLOOKUP then. This doesn't work so I see there must be a way for this.

I would appreciate your help!

 

[Fluff]

I'm running 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	Client	Salesperson	Sales ($)			Client	Frequent/Max Salesperson	Sales ($)
2	Google	Mike	1,000			Google	Mike	1000
3	Apple	Jim	5,000			Apple	George	7000
4	Apple	George	4,000
5	Apple	George	3,000
6
7
8
9
Main

Cell Formulas
Range	Formula
F2:F3	F2	=UNIQUE(FILTER(A2:A100,A2:A100<>""))
G2:H3	G2	=LET(u,UNIQUE(FILTER($B$2:$B$100,$A$2:$A$100=F2)),INDEX(SORT(CHOOSE({1,2},u,SUMIFS($C$2:$C$100,$B$2:$B$100,u)),2,-1),1,{1,2}))
Dynamic array formulas.

 

[meamuka]

Thank you Sir this works great!

I had the "Sales ($)' Column already doing another calculation, so I truncated your formula to do just my second column.

Thanks again!

 

[Fluff]

You're welcome & thanks for the feedback.