Showing the data with the largest amount based on three criteria

[crazytimechris]

Hello,

I have three colums of information. Customer, Product, and revenue, I would like to show which customer has generated the most revenue on each product and overall which customer has generated the most revenue.
I know how to sumifs and show based on the customer the information I want, but I want to be shown the customer. I have attached an example and hope it helps.

Thanks a lot in advance

 

[crazytimechris]

I dont know what you are saying here. is what I want to do impossible then?

 

[Jimmypop]

Maybe the below will work... I made a pivot table to couble check and it returns the customer with the highest amount of revenue...

Book1.xlsx
	A	B	C	D	E	F	G
1	DATE	CLIENT	PRODUCT	REVENUE	Date:	23/03/2023
2	23/03/2023	Client 2	Product 1	167333
3	23/03/2023	Client 3	Product 1	171130	Customer with most revenue		Client 3
4	23/03/2023	Client 4	Product 1	124667
5	23/03/2023	Client 5	Product 1	191862
6	23/03/2023	Client 6	Product 1	84156
7	23/03/2023	Client 7	Product 1	108104
8	23/03/2023	Client 8	Product 1	146960
9	23/03/2023	Client 9	Product 2	133897
10	23/03/2023	Client 10	Product 1	172430
11	23/03/2023	Client 1	Product 2	108582
12	23/03/2023	Client 2	Product 2	43558
13	23/03/2023	Client 3	Product 2	60780
14	23/03/2023	Client 4	Product 2	91543
15	23/03/2023	Client 5	Product 2	158497
16	23/03/2023	Client 6	Product 2	17886
17	23/03/2023	Client 7	Product 2	154576
18	23/03/2023	Client 8	Product 2	56951
19	23/03/2023	Client 9	Product 2	87921
20	23/03/2023	Client 10	Product 2	52230
21	23/03/2023	Client 1	Product 3	176221
22	23/03/2023	Client 2	Product 3	47052
23	23/03/2023	Client 3	Product 3	192593
24	23/03/2023	Client 4	Product 3	182595
25	23/03/2023	Client 5	Product 3	20634
26	23/03/2023	Client 6	Product 3	176409
27	23/03/2023	Client 7	Product 3	114572
28	23/03/2023	Client 8	Product 3	122348
29	23/03/2023	Client 9	Product 3	70081
30	23/03/2023	Client 10	Product 3	59691
Sheet1

Cell Formulas
Range	Formula
G3	G3	=INDEX(Table1[CLIENT],MATCH(MAX(SUMIF(Table1[CLIENT],Table1[CLIENT],Table1[REVENUE])),SUMIF(Table1[CLIENT],Table1[CLIENT],Table1[REVENUE]),0))

Book1.xlsx
	K	L
6	Row Labels	Sum of REVENUE
7	23/03/2023	3295259
8	Client 3	424503
9	Client 4	398805
10	Client 7	377252
11	Client 5	370993
12	Client 8	326259
13	Client 9	291899
14	Client 1	284803
15	Client 10	284351
16	Client 6	278451
17	Client 2	257943
18	Grand Total	3295259
Sheet1

 

[Fluff]

@crazytimechris
I suggest that you update your Account details (or click your user name at the top right of the forum) to show that you are using 2021 & a Mac, 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

=LET(u,UNIQUE(FILTER(C5:C100,C5:C100<>"")),INDEX(SORTBY(u,SUMIFS(E:E,C:C,u),-1),1))