Count and then Rank multiple text values in same Cell in one Column
I have been scouring the community page looking for help with this question and plan to set up a Pro Desk appointment, but they don't have any availability until next week so any help is appreciated.
I have a form collecting data into a sheet and then a separate calculations sheet. I am trying to 'count' within a column the number of times a topic was selected, and then sort/rank 1st, 2nd, 3rd picks. My problem is that there are multiple text values in the same cell.
The formula I am using only takes the first topic selected and doesn't account for the other topics selected in that cell.
=COUNTIF({Safety Training Topics - Flight Safety Categories}, "Other")
Screenshot of Form Data Column:
Screenshot of Results:
Best Answer
-
Try this:
=COUNTIF({Safety Training Topics - Flight Safety Categories}, HAS(@cell,"Other"))
Answers
-
Try this:
=COUNTIF({Safety Training Topics - Flight Safety Categories}, HAS(@cell,"Other"))
-
Technically (assuming that works for you), you can improve the formula by replacing: "other" with Primary@row. (Assuming your primary column is still named "Primary". Then you could make that a column formula.
-
The formula worked to get the count, THANK YOU! How do I apply it to the rest of the topics without typing each one out??
-
ALSO - Once I get my results, how would I rank/sort them?
-
So if you replace the "Other" part of your formula with a reference to the primary cell at that row, then either make it a column formula or copy/paste it would work for all the other rows.
As far as the rank, you'd want another column for that using either the RankAvg() or RankEQ() formula (prob the EQ). It would look something like :
=rankeq(count@row,countcolumn:countcolumn) Make this a column formula or a copy/paste on the rows you need it on.Replace count@row and countcolumn:countcolumn with whatever your column names are.
-
This is the formula I used to get 1st…
=INDEX({Safety Training Topics - Hot Topics Range 2}, MATCH(LARGE([Count Results]2:[Count Results]4, 1), [Count Results]2:[Count Results]4, 0))
And this is what I used to get 2nd…
=INDEX({Safety Training Topics - Hot Topics Range 2}, MATCH(LARGE([Count Results]2:[Count Results]4, 2), [Count Results]2:[Count Results]4, 0))
But it doesn't work for 3rd? What am I doing wrong
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!