Find Top 10 customers based on sales columns

[Bond00]

So i have all the info of quotes listed.

I'd like to have info over on the right where it gets the top 10 customers based on total revenue, and then another top 10 based on total actual profit etc.

Now keep in mind, Col G is the total revenue in the whole quote, also counting the monthly and yearly #'s as well in with it. Col H for profit also includes the monthly and yrly profit as well. (not the total for 12 months on the monthly just the first month would be factored into the revenue in col G and profit in H col)

Also be aware of this:
Won = Won, they got it and paid
WonO = it was a 2nd or 3rd quote Option that didn't win, but didn't want it to count as Won or Lost in other tally's. Also don't count it as revenue or profit, its only there for record it counts towards nothing.
Delayed = maybe later
Lost = Lost/dead quote
LostO = it was a 2nd or 3rd quote Option that also lost, but didn't want it to count as Lost other metrics for % ratio lost etc.. its only there for record it counts towards nothing.

Something like this as output:

Also I'd like more than 1 chart of total revenue, 1 based on quotes won and 1 based on total revenue counting anything except Lost, LostO, and WonO

Would it also be possible to have it based on a year range, so i guess looking like this. Where it will also count everything in yrs 2017 and 2018, if both were 2017 then it would only count that 1 year Jan1-Dec31.
Based on Closed dates column. This way i can look at different data on the fly changing the yrs.

 

[etaf]

as you have asked for quite a number of things, using all your data - rather then me having to type all that information into a spreadsheet myself,
which i'm afraid i wont be doing

you sample only has 10 customers - so they all will be shown - maybe a sample with more customers

Also you have 2 versions of excel in your profile - WHICH version will this be used on

I'm thinking some pivot tables may be the easiest way to go

can you -

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.

This will possibly enable a quicker and more accurate solution for you.

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

 

[Bond00]

Ok here is the data to work with. There is no formulas in this as its a bunch of data all pulled from other xls sheets in a folder and vba used to paste just the values here on this sheet to then work with.
Also best to use 2016 for this one just because some use 2016 still and backwards compatibility would be nice to keep on that version for now. If there is a much better easier way of doing this from a function in 2021 though, let me know.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	File name	Quote	Customer Name	Discription	Started	Closed	Amount	Profit	Monthly	Mthly Prof	Yearly	Yrly Prof	W / L
2	name	Q0241	Customer 1	Annual renewal for AntiVirus	9/19/2017	9/19/2017	$135.42	$3.42	$0.00	$0.00	$0.00	$0.00	Won
3	name	Q0242	Customer 2	New Server	9/18/2017	9/22/2017	$22.57	$0.57	$0.00	$0.00	$0.00		Delayed
4	name	Q0243	Customer 3	Cloud BU	9/21/2017		$1,013.40	$340.90	$750.00	$300.00	$0.00		0
5	name	Q0244	Customer 3	New Server	9/25/2017	10/19/2017	$18,426.00	$7,686.00	$0.00	$0.00	$0.00		Won
6	name	Q0245	Customer 4	Annual renewal for AntiVirus	9/21/2017	10/9/2017	$1,287.00	$187.00	$0.00	$0.00	$0.00		Won
7	name	Q0246	Customer 5	Annual renewal for AntiVirus	9/21/2017	9/29/2017	$211.00	$145.00	$0.00	$0.00	$0.00		Won
8	name	Q0247	Customer 6	Data Switch	9/21/2017	10/1/2017	$648.20	$88.20	$0.00	$0.00	$0.00		Won
9	name	Q0248	Customer 7	Barracuda Backup 190 1 Yr	9/22/2017	9/26/2017	$2,439.05	$865.05	$0.00	$0.00	$0.00		Won
10	name	Q0249	Customer 7	Barracuda Backup 190 5 Yr	9/22/2017	9/26/2017	$1,786.38	$311.38	$0.00	$0.00	$0.00		WonO
11	name	Q0251	Customer 8	Annual renewal for AntiVirus	9/25/2017		$403.00	$183.00	$0.00	$0.00	$0.00		0
12	name	Q0252	Customer 9	Server (No encrypted drives)	9/25/2017	9/29/2017	$10,505.61	$3,112.41	$0.00	$0.00	$0.00		Won
13	name	Q0253	Customer 5	Barracuda Backup	9/25/2017	9/29/2017	$7,555.75	$1,056.39	$0.00	$0.00	$0.00		Lost
14	name	Q0254	Customer 9	Server (Encrypted drives)	9/25/2017	9/29/2017	$13,281.48	$3,469.48	$0.00	$0.00	$0.00		LostO
15	name	Q0255	Customer 10	Barracuda Backup	9/5/2017		$24,392.86	$5,176.90	$0.00	$13,200.00	$13,200.00	$2,640.00	0
16	name	Q0256	Customer 11	items	9/26/2017	9/30/2017	$1,000.00	$250.00	$0.00	$0.00	$0.00	$0.00	won
17	name	Q0257	Customer 12	items	9/26/2017		$1,500.00	$500.00	$0.00	$0.00	$0.00	$0.00
18	name	Q0258	Customer 13	items	9/26/2017	10/2/2017	$2,000.00	$750.00	$0.00	$0.00	$0.00	$0.00	won
19	name	Q0259	Customer 14	items	9/26/2017	10/3/2017	$2,500.00	$1,000.00	$0.00	$0.00	$0.00	$0.00	won
20	name	Q0260	Customer 15	items	9/26/2017	10/4/2017	$3,000.00	$1,250.00	$0.00	$0.00	$0.00	$0.00	won
21	name	Q0261	Customer 3	items	9/26/2017	10/5/2017	$3,500.00	$1,500.00	$0.00	$0.00	$0.00	$0.00	wonO
22	name	Q0262	Customer 9	items	9/27/2017		$4,000.00	$1,750.00	$0.00	$0.00	$0.00	$0.00
23	name	Q0263	Customer 16	items	9/28/2017	10/7/2017	$4,500.00	$2,000.00	$0.00	$0.00	$0.00	$0.00	lost
24	name	Q0264	Customer 17	items	9/29/2017	10/8/2017	$5,000.00	$2,250.00	$0.00	$0.00	$0.00	$0.00	won
25	name	Q0265	Customer 12	items	9/30/2017		$5,500.00	$2,500.00	$0.00	$0.00	$0.00	$0.00
26	name	Q0266	Customer 1	items	10/1/2017		$6,000.00	$2,500.00	$0.00	$0.00	$0.00	$0.00
27	name	Q0267	Customer 18	items	10/2/2017	10/11/2017	$6,500.00	$2,500.00	$0.00	$0.00	$0.00	$0.00	won
28	name	Q0268	Customer 20	items	10/3/2017	10/12/2017	$7,000.00	$2,500.00	$0.00	$0.00	$0.00	$0.00	lost
Sheet1

 

[Bond00]

Also there is nothing to the right of col M so figured the stuff could go over there for this. I can see about 6 columns over there since i have a 2k screen for it.

 

[etaf]

i have just done revenue and profit , using a pivot table & top10 results

the total of the column G and H

Now keep in mind, Col G is the total revenue in the whole quote, also counting the monthly and yearly #'s as well in with it. Col H for profit also includes the monthly and yrly profit as well. (not the total for 12 months on the monthly just the first month would be factored into the revenue in col G and profit in H col)

Didnt understand the above

before spending time and looking at all the other requirements and see if a pivot table will also work for those
I wanted to make sure that works for you

its 2 pivot tables , so I suspect will need additional pivot tables or formulas in the rest

And they will need to be filtered for the various criteria , not used top10 with filters , so not sure how that will work

BUT as you only have 6 columns , then i was not sure this would work, as you need 4 columns just for this maybe 5 with a break between

Not sure if 2021 has all the latest LET functions - I suspect one of the members would be able to write a LET that will output everything in an array from 1 formula

see what you think of this


added onto a share - but only for a few days

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	File name	Quote	Customer Name	Discription	Started	Closed	Amount	Profit	Monthly	Mthly Prof	Yearly	Yrly Prof	W / L		Revenue			Profit
2	name	Q0241	Customer 1	Annual renewal for AntiVirus	42997	42997	135.42	3.42	0	0	0	0	Won		Row Labels	Sum of Amount		Row Labels	Sum of Profit
3	name	Q0242	Customer 2	New Server	42996	43000	22.57	0.57	0	0	0		Delayed		Customer 9	$27,787.09		Customer 9	$8,331.89
4	name	Q0243	Customer 3	Cloud BU	42999		1013.4	340.9	750	300	0		0		Customer 10	$24,392.86		Customer 3	$9,526.90
5	name	Q0244	Customer 3	New Server	43003	43027	18426	7686	0	0	0		Won		Customer 3	$22,939.40		Customer 20	$2,500.00
6	name	Q0245	Customer 4	Annual renewal for AntiVirus	42999	43017	1287	187	0	0	0		Won		Customer 5	$7,766.75		Customer 18	$2,500.00
7	name	Q0246	Customer 5	Annual renewal for AntiVirus	42999	43007	211	145	0	0	0		Won		Customer 12	$7,000.00		Customer 17	$2,250.00
8	name	Q0247	Customer 6	Data Switch	42999	43009	648.2	88.2	0	0	0		Won		Customer 20	$7,000.00		Customer 16	$2,000.00
9	name	Q0248	Customer 7	Barracuda Backup 190 1 Yr	43000	43004	2439.05	865.05	0	0	0		Won		Customer 18	$6,500.00		Customer 15	$1,250.00
10	name	Q0249	Customer 7	Barracuda Backup 190 5 Yr	43000	43004	1786.38	311.38	0	0	0		WonO		Customer 1	$6,135.42		Customer 12	$3,000.00
11	name	Q0251	Customer 8	Annual renewal for AntiVirus	43003		403	183	0	0	0		0		Customer 17	$5,000.00		Customer 10	$5,176.90
12	name	Q0252	Customer 9	Server (No encrypted drives)	43003	43007	10505.61	3112.41	0	0	0		Won		Customer 16	$4,500.00		Customer 1	$2,503.42
13	name	Q0253	Customer 5	Barracuda Backup	43003	43007	7555.75	1056.39	0	0	0		Lost		Grand Total	$119,021.52		Grand Total	$39,039.11
14	name	Q0254	Customer 9	Server (Encrypted drives)	43003	43007	13281.48	3469.48	0	0	0		LostO
15	name	Q0255	Customer 10	Barracuda Backup	42983		24392.858	5176.9	0	13200	13200	2640	0
16	name	Q0256	Customer 11	items	43004	43008	1000	250	0	0	0	0	won
17	name	Q0257	Customer 12	items	43004		1500	500	0	0	0	0
18	name	Q0258	Customer 13	items	43004	43010	2000	750	0	0	0	0	won
19	name	Q0259	Customer 14	items	43004	43011	2500	1000	0	0	0	0	won
20	name	Q0260	Customer 15	items	43004	43012	3000	1250	0	0	0	0	won
21	name	Q0261	Customer 3	items	43004	43013	3500	1500	0	0	0	0	wonO
22	name	Q0262	Customer 9	items	43005		4000	1750	0	0	0	0
23	name	Q0263	Customer 16	items	43006	43015	4500	2000	0	0	0	0	lost
24	name	Q0264	Customer 17	items	43007	43016	5000	2250	0	0	0	0	won
25	name	Q0265	Customer 12	items	43008		5500	2500	0	0	0	0
26	name	Q0266	Customer 1	items	43009		6000	2500	0	0	0	0
27	name	Q0267	Customer 18	items	43010	43019	6500	2500	0	0	0	0	won
28	name	Q0268	Customer 20	items	43011	43020	7000	2500	0	0	0	0	lost
Sheet1

Dropbox

www.dropbox.com

 

[Bond00]

It doesn't look like its doing it 100%, also sadly I don't really understand how pivot tables work or how to set them up.
But what i need is maybe a sumproduct or sumif? not sure..
I need the top 10 customers that have the highest total amount in col G and also if col M= "Won"
I think customer 3 is the highest since it has the highest total amount in all of col G with the given row also = "Won" in col M
I hope i explained that well enough?

I'd also like it to reference 2 cells (like P1 and Q1) that will have a start date and end date in it, and have it tally it up using that date range as criteria based on column F.

 

[etaf]

i have changed to include the W?L column and filter on WON

I'm not sure how to do top10 with a function in 2016 , so i may look into later in week, or hopefully another member will help

Top10-ETAF.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	File name	Quote	Customer Name	Discription	Started	Closed	Amount	Profit	Monthly	Mthly Prof	Yearly	Yrly Prof	W / L		Revenue				Profit
2	name	Q0241	Customer 1	Annual renewal for AntiVirus	42997	42997	135.42	3.42	0	0	0	0	Won		W / L	Customer Name	Sum of Amount		W / L	Customer Name	Sum of Profit
3	name	Q0242	Customer 2	New Server	42996	43000	22.57	0.57	0	0	0		Delayed		Won	Customer 3	$18,426.00		Won	Customer 3	$7,686.00
4	name	Q0243	Customer 3	Cloud BU	42999		1013.4	340.9	750	300	0		0			Customer 9	$10,505.61			Customer 9	$3,112.41
5	name	Q0244	Customer 3	New Server	43003	43027	18426	7686	0	0	0		Won			Customer 18	$6,500.00			Customer 18	$2,500.00
6	name	Q0245	Customer 4	Annual renewal for AntiVirus	42999	43017	1287	187	0	0	0		Won			Customer 17	$5,000.00			Customer 17	$2,250.00
7	name	Q0246	Customer 5	Annual renewal for AntiVirus	42999	43007	211	145	0	0	0		Won			Customer 15	$3,000.00			Customer 15	$1,250.00
8	name	Q0247	Customer 6	Data Switch	42999	43009	648.2	88.2	0	0	0		Won			Customer 14	$2,500.00			Customer 14	$1,000.00
9	name	Q0248	Customer 7	Barracuda Backup 190 1 Yr	43000	43004	2439.05	865.05	0	0	0		Won			Customer 7	$2,439.05			Customer 7	$865.05
10	name	Q0249	Customer 7	Barracuda Backup 190 5 Yr	43000	43004	1786.38	311.38	0	0	0		WonO			Customer 13	$2,000.00			Customer 13	$750.00
11	name	Q0251	Customer 8	Annual renewal for AntiVirus	43003		403	183	0	0	0		0			Customer 4	$1,287.00			Customer 11	$250.00
12	name	Q0252	Customer 9	Server (No encrypted drives)	43003	43007	10505.61	3112.41	0	0	0		Won			Customer 11	$1,000.00			Customer 4	$187.00
13	name	Q0253	Customer 5	Barracuda Backup	43003	43007	7555.75	1056.39	0	0	0		Lost
14	name	Q0254	Customer 9	Server (Encrypted drives)	43003	43007	13281.48	3469.48	0	0	0		LostO
15	name	Q0255	Customer 10	Barracuda Backup	42983		24392.858	5176.9	0	13200	13200	2640	0
16	name	Q0256	Customer 11	items	43004	43008	1000	250	0	0	0	0	won
17	name	Q0257	Customer 12	items	43004		1500	500	0	0	0	0
18	name	Q0258	Customer 13	items	43004	43010	2000	750	0	0	0	0	won
19	name	Q0259	Customer 14	items	43004	43011	2500	1000	0	0	0	0	won
20	name	Q0260	Customer 15	items	43004	43012	3000	1250	0	0	0	0	won
21	name	Q0261	Customer 3	items	43004	43013	3500	1500	0	0	0	0	wonO
22	name	Q0262	Customer 9	items	43005		4000	1750	0	0	0	0
23	name	Q0263	Customer 16	items	43006	43015	4500	2000	0	0	0	0	lost
24	name	Q0264	Customer 17	items	43007	43016	5000	2250	0	0	0	0	won
25	name	Q0265	Customer 12	items	43008		5500	2500	0	0	0	0
26	name	Q0266	Customer 1	items	43009		6000	2500	0	0	0	0
27	name	Q0267	Customer 18	items	43010	43019	6500	2500	0	0	0	0	won
28	name	Q0268	Customer 20	items	43011	43020	7000	2500	0	0	0	0	lost
29
Sheet1

Dropbox

www.dropbox.com

 

[Bond00]

Yeah that seems to work, I just dont know how to set that up in my real sheet, and also it doesn't seem to update with live cell changes unless i resort it?
This sheet i have a button i press that updates the sheet and pulls all the values from many other files to make a large list in these columns A1:M600+

Also is the date sort thing possible?
"I'd also like it to reference 2 cells (like P1 and Q1) that will have a start date and end date in it, and have it tally it up using that date range as criteria based on column F."

 

[etaf]

each pivot table will need to be refreshed, i used column reference - so range was not important - BUT that will slow the spredsheet down - there is ! to do that

maybe you need a VBA / Macro to do all this - NOT something i provide on forums

i'll look at things like functions to do that - BUT to work in 2016 will need older functions - so will have to look over the coming days and see what i can find, if anything

 

[Bond00]

I guess the pivot table works for what i need out of this pretty well, so i guess no need to try and figure out a formula for it. thanks