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



  • SoS | Dan Palenchar
    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

    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!

    PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!

  • 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!