Rank Top 20 Clients By Value of Won Opportunities
We have a sheet we use to track opportunities. I'm trying to figure out a formula to create a list of top clients by the value of won opportunities in a metrics sheet so that I can add the list to a dashboard.
Above is an example of the data. The list of clients changes so the formula needs to by dynamic. I want to show the top clients and the total value of wins with them.
I'd also like to be able to show win ratios for all clients i.e. value of won vs value of lost and then rank the clients with the highest ratio of lost vs won.
I hope all this makes sense. TIA
Answers
-
Make a new column, I'll call it "Client Rank". Input below formula
=RANKEQ([Forecast Revenue Amount (NDY FEE)]@row, [Forecast Revenue Amount (NDY FEE)]:[Forecast Revenue Amount (NDY FEE)])
To get win ratio, you'll need to be tracking in your opportunities (or other Sheets) if the job was won/lost. I'm going to assume you're doing this in a job called "Status". You can to do this in a single column by counting all "Won" Statuses and dividing by the sum of "Won" and "Lost" Statuses. It might be easier to make columns that show the number of Wins and Losses for each clients (and more insightful) and then compute win ratio of that.
For won/lost opportunities use below formula (replace "Won" with "Lost", or whatever text you're using). Let's assume you make two columns called "# Won" and "# Lost":
=COUNTIFS({Status on other Sheet}, "Won", {Primary Client column on other Sheet}, [Primary Client]@row@rowan.bradley
Then, in "Win Ratio" column, simply do:
=IFERROR([# Won]@row/SUM([# Won]@row, [# Lost]@row), "N/A")
The if error will prevent a #DIVIDE BY ZERO error in the event that the client has no opportunities listed. It's not really necessary but it looks cleaner IMO
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
-
The ranking of clients by fee value works.
I am having trouble with the win ratios though. Our statuses are captured in a single column, the column labeled "Sales Stage". We capture them as "Closed Lost" or "Closed Won". I have added a new column to my sheet labelled "Client Win Loss Ratio" and I added your first formula excluding the reference to another sheet as the data is included on the one sheet:
=COUNTIFS([Sales Stage]@row, "Closed Lost", [Primary Client]@row)
I get an #incorrect argument.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 360 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!