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

 

[Jimmypop]

Hi there

Maybe try the below... If you do not have your data in a table format you cun just update the references in formula with those of the cell ranges which contain your data...

Book1
	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(Table1[REVENUE]),Table1[REVENUE],0))

 

[etaf]

what version of excel do you have ?

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[crazytimechris]

Im sorry, I made the example spreadsheet to upload as an example but had trouble with XL2BB so copied the image so you could at least see what I meant.

 

[etaf]

but still dont know the version of excel -

 

[crazytimechris]

Hi there

Maybe try the below... If you do not have your data in a table format you cun just update the references in formula with those of the cell ranges which contain your data...

Book1
	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(Table1[REVENUE]),Table1[REVENUE],0))

Thanks a lot for this, but In your example I think you showed me how to find which client simply has the cell with the highest revenue whereas I want to see which client earned the most, irrespective of the product (eventually split by date if you might know how to do that as well )

Ive added a link to my working example file here: Loading Google Sheets

thanks a lot!

 

[crazytimechris]

but still dont know the version of excel -

Apologies

 

[Jimmypop]

I did it on Excel for Windows... Not sure about for Mac...

 

[crazytimechris]

I did it on Excel for Windows... Not sure about for Mac...

Arent the formulas the same regardless? I mean, my example is in google sheets now to excel is irrelevant.

 

[etaf]

yes, most will be the same with mac version . some things are not quite the same still -
BUT you have 365 version and so a lot of the latest functions filter etc