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

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.

Find more posts tagged with

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.