Find Top 10 customers based on sales columns

Bond00

Board Regular
Joined
Oct 11, 2017
Messages
142
Office Version
  1. 2021
  2. 2016
Platform
  1. Windows
So i have all the info of quotes listed.
1727766756156.png


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:
1727767333266.png


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.
1727767992579.png
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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.

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

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
 
Upvote 0
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
ABCDEFGHIJKLMN
1File nameQuoteCustomer NameDiscriptionStartedClosedAmountProfitMonthlyMthly ProfYearlyYrly ProfW / L
2nameQ0241Customer 1Annual renewal for AntiVirus9/19/20179/19/2017$135.42$3.42$0.00$0.00$0.00$0.00Won
3nameQ0242Customer 2New Server9/18/20179/22/2017$22.57$0.57$0.00$0.00$0.00Delayed
4nameQ0243Customer 3Cloud BU9/21/2017$1,013.40$340.90$750.00$300.00$0.000
5nameQ0244Customer 3New Server9/25/201710/19/2017$18,426.00$7,686.00$0.00$0.00$0.00Won
6nameQ0245Customer 4Annual renewal for AntiVirus9/21/201710/9/2017$1,287.00$187.00$0.00$0.00$0.00Won
7nameQ0246Customer 5Annual renewal for AntiVirus9/21/20179/29/2017$211.00$145.00$0.00$0.00$0.00Won
8nameQ0247Customer 6Data Switch9/21/201710/1/2017$648.20$88.20$0.00$0.00$0.00Won
9nameQ0248Customer 7Barracuda Backup 190 1 Yr9/22/20179/26/2017$2,439.05$865.05$0.00$0.00$0.00Won
10nameQ0249Customer 7Barracuda Backup 190 5 Yr9/22/20179/26/2017$1,786.38$311.38$0.00$0.00$0.00WonO
11nameQ0251Customer 8Annual renewal for AntiVirus9/25/2017$403.00$183.00$0.00$0.00$0.000
12nameQ0252Customer 9Server (No encrypted drives)9/25/20179/29/2017$10,505.61$3,112.41$0.00$0.00$0.00Won
13nameQ0253Customer 5Barracuda Backup9/25/20179/29/2017$7,555.75$1,056.39$0.00$0.00$0.00Lost
14nameQ0254Customer 9Server (Encrypted drives)9/25/20179/29/2017$13,281.48$3,469.48$0.00$0.00$0.00LostO
15nameQ0255Customer 10Barracuda Backup9/5/2017$24,392.86$5,176.90$0.00$13,200.00$13,200.00$2,640.000
16nameQ0256Customer 11items9/26/20179/30/2017$1,000.00$250.00$0.00$0.00$0.00$0.00won
17nameQ0257Customer 12items9/26/2017$1,500.00$500.00$0.00$0.00$0.00$0.00
18nameQ0258Customer 13items9/26/201710/2/2017$2,000.00$750.00$0.00$0.00$0.00$0.00won
19nameQ0259Customer 14items9/26/201710/3/2017$2,500.00$1,000.00$0.00$0.00$0.00$0.00won
20nameQ0260Customer 15items9/26/201710/4/2017$3,000.00$1,250.00$0.00$0.00$0.00$0.00won
21nameQ0261Customer 3items9/26/201710/5/2017$3,500.00$1,500.00$0.00$0.00$0.00$0.00wonO
22nameQ0262Customer 9items9/27/2017$4,000.00$1,750.00$0.00$0.00$0.00$0.00
23nameQ0263Customer 16items9/28/201710/7/2017$4,500.00$2,000.00$0.00$0.00$0.00$0.00lost
24nameQ0264Customer 17items9/29/201710/8/2017$5,000.00$2,250.00$0.00$0.00$0.00$0.00won
25nameQ0265Customer 12items9/30/2017$5,500.00$2,500.00$0.00$0.00$0.00$0.00
26nameQ0266Customer 1items10/1/2017$6,000.00$2,500.00$0.00$0.00$0.00$0.00
27nameQ0267Customer 18items10/2/201710/11/2017$6,500.00$2,500.00$0.00$0.00$0.00$0.00won
28nameQ0268Customer 20items10/3/201710/12/2017$7,000.00$2,500.00$0.00$0.00$0.00$0.00lost
Sheet1
 
Upvote 0
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.
 
Upvote 0
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
ABCDEFGHIJKLMNOPQRST
1File nameQuoteCustomer NameDiscriptionStartedClosedAmountProfitMonthlyMthly ProfYearlyYrly ProfW / L RevenueProfit
2nameQ0241Customer 1Annual renewal for AntiVirus4299742997135.423.420000WonRow LabelsSum of AmountRow LabelsSum of Profit
3nameQ0242Customer 2New Server429964300022.570.57000DelayedCustomer 9$27,787.09Customer 9$8,331.89
4nameQ0243Customer 3Cloud BU429991013.4340.975030000Customer 10$24,392.86Customer 3$9,526.90
5nameQ0244Customer 3New Server4300343027184267686000WonCustomer 3$22,939.40Customer 20$2,500.00
6nameQ0245Customer 4Annual renewal for AntiVirus42999430171287187000WonCustomer 5$7,766.75Customer 18$2,500.00
7nameQ0246Customer 5Annual renewal for AntiVirus4299943007211145000WonCustomer 12$7,000.00Customer 17$2,250.00
8nameQ0247Customer 6Data Switch4299943009648.288.2000WonCustomer 20$7,000.00Customer 16$2,000.00
9nameQ0248Customer 7Barracuda Backup 190 1 Yr43000430042439.05865.05000WonCustomer 18$6,500.00Customer 15$1,250.00
10nameQ0249Customer 7Barracuda Backup 190 5 Yr43000430041786.38311.38000WonOCustomer 1$6,135.42Customer 12$3,000.00
11nameQ0251Customer 8Annual renewal for AntiVirus430034031830000Customer 17$5,000.00Customer 10$5,176.90
12nameQ0252Customer 9Server (No encrypted drives)430034300710505.613112.41000WonCustomer 16$4,500.00Customer 1$2,503.42
13nameQ0253Customer 5Barracuda Backup43003430077555.751056.39000LostGrand Total$119,021.52Grand Total$39,039.11
14nameQ0254Customer 9Server (Encrypted drives)430034300713281.483469.48000LostO
15nameQ0255Customer 10Barracuda Backup4298324392.8585176.90132001320026400
16nameQ0256Customer 11items430044300810002500000won
17nameQ0257Customer 12items4300415005000000
18nameQ0258Customer 13items430044301020007500000won
19nameQ0259Customer 14items4300443011250010000000won
20nameQ0260Customer 15items4300443012300012500000won
21nameQ0261Customer 3items4300443013350015000000wonO
22nameQ0262Customer 9items43005400017500000
23nameQ0263Customer 16items4300643015450020000000lost
24nameQ0264Customer 17items4300743016500022500000won
25nameQ0265Customer 12items43008550025000000
26nameQ0266Customer 1items43009600025000000
27nameQ0267Customer 18items4301043019650025000000won
28nameQ0268Customer 20items4301143020700025000000lost
Sheet1


 
Upvote 0
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.
 
Upvote 0
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
ABCDEFGHIJKLMNOPQRSTUVW
1File nameQuoteCustomer NameDiscriptionStartedClosedAmountProfitMonthlyMthly ProfYearlyYrly ProfW / L RevenueProfit
2nameQ0241Customer 1Annual renewal for AntiVirus4299742997135.423.420000WonW / L Customer NameSum of AmountW / L Customer NameSum of Profit
3nameQ0242Customer 2New Server429964300022.570.57000DelayedWonCustomer 3$18,426.00WonCustomer 3$7,686.00
4nameQ0243Customer 3Cloud BU429991013.4340.975030000Customer 9$10,505.61Customer 9$3,112.41
5nameQ0244Customer 3New Server4300343027184267686000WonCustomer 18$6,500.00Customer 18$2,500.00
6nameQ0245Customer 4Annual renewal for AntiVirus42999430171287187000WonCustomer 17$5,000.00Customer 17$2,250.00
7nameQ0246Customer 5Annual renewal for AntiVirus4299943007211145000WonCustomer 15$3,000.00Customer 15$1,250.00
8nameQ0247Customer 6Data Switch4299943009648.288.2000WonCustomer 14$2,500.00Customer 14$1,000.00
9nameQ0248Customer 7Barracuda Backup 190 1 Yr43000430042439.05865.05000WonCustomer 7$2,439.05Customer 7$865.05
10nameQ0249Customer 7Barracuda Backup 190 5 Yr43000430041786.38311.38000WonOCustomer 13$2,000.00Customer 13$750.00
11nameQ0251Customer 8Annual renewal for AntiVirus430034031830000Customer 4$1,287.00Customer 11$250.00
12nameQ0252Customer 9Server (No encrypted drives)430034300710505.613112.41000WonCustomer 11$1,000.00Customer 4$187.00
13nameQ0253Customer 5Barracuda Backup43003430077555.751056.39000Lost
14nameQ0254Customer 9Server (Encrypted drives)430034300713281.483469.48000LostO
15nameQ0255Customer 10Barracuda Backup4298324392.8585176.90132001320026400
16nameQ0256Customer 11items430044300810002500000won
17nameQ0257Customer 12items4300415005000000
18nameQ0258Customer 13items430044301020007500000won
19nameQ0259Customer 14items4300443011250010000000won
20nameQ0260Customer 15items4300443012300012500000won
21nameQ0261Customer 3items4300443013350015000000wonO
22nameQ0262Customer 9items43005400017500000
23nameQ0263Customer 16items4300643015450020000000lost
24nameQ0264Customer 17items4300743016500022500000won
25nameQ0265Customer 12items43008550025000000
26nameQ0266Customer 1items43009600025000000
27nameQ0267Customer 18items4301043019650025000000won
28nameQ0268Customer 20items4301143020700025000000lost
29
Sheet1




 
Upvote 0
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."
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,224,568
Messages
6,179,572
Members
452,927
Latest member
whitfieldcraig

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top