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
 
ok, glad a pivot table works for you
 
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Forum statistics

Threads
1,224,567
Messages
6,179,568
Members
452,926
Latest member
rows and columns

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