Rank Top 20 Clients By Value of Won Opportunities

Sarah Dawson

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

SoS | Dan Palenchar

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

Sarah Dawson

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.