How to count and rank top 10 from one column of data

Options

Hello. I am trying to create some KPIs in Smartsheet and roll up to a dashboard.

In a column I have a running bid board with customers listed ("Customer Name") as the bids accumulate. In a separate sheet where I am rolling up KPI calculations I want a live count of how many bids (or instances) that customer is listed in that column. This would be very simple to do with a pivot table of course, but cannot figure out how to "roll-up" or aggregate the duplications for lack of better words. In the end I am trying to get a count and rank in order to list out Top 10 customers bidding with us.

I am trying to avoid a standard COUNTIFS formula that is static around key words because the customer list will add new customers all the time and I would like to see how the move up and down the ranking... I hope this makes sense.


Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Options

    Try incorporating the RANKQ function (https://help.smartsheet.com/function/rankeq)


    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

  • Anthony Lunn
    Options

    Dan Palenchar - I think this only applies to numbers from what I can tell. I am using customer names. Can you confirm?

  • Leslie R
    Leslie R Employee
    Options

    Hello @Anthony Lunn,

    I believe I was able to find a way to gather the rank based on Dan's previous answer by doing a little manual work using your Customer Name information.

    • I created a mock sheet and entered the values for your Customer Name column.
    • I created a copy of the mock sheet in order to keep the original version for a COUNTIF and RANKEQ functions.
    • Once that was done, I created a checkbox column that would utilize a formula to check the box if there were duplicate values in the Customer Name column.
      • =IF(COUNTIF([Customer Name]:[Customer Name], =[Customer Name]@row) > 1, 1)
    • Then I filtered the sheet, for each set of values and deleted any duplicate rows. I continued this process until all of the checkboxes were blank, indicating there were no longer duplicates on my sheet.
    • Once that was done, I created a Count column to create a cross sheet COUNTIF formula to return the number of times the Customer Name is returned from the original sheet.
      • =COUNTIF({Projects House Customer Name}, [Customer Name]@row)
      • {Projects House Customer Name} is referencing the entire column on the original mock sheet. This will ensure that any new entries will also be included in the COUNTIF formula.
    • Finally, I created a Rank column to reference the Count column and rank the customer names.
      • =RANKEQ(Count@row, Count:Count)

    I hope this information helps!

    Cheers,

    Leslie

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!