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

Anthony Lunn

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.

SoS | Dan Palenchar

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

Anthony Lunn

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

Leslie R

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