Count Response Occurrence from Multiselect Drop Down
Hi,
I have a project intake sheet that has a column for allocating 'tags' to the project to define subject areas included in the project. Each project can have multiple 'tags'. See below:
Is there a formula that will dynamically populate a helper sheet with all the 'tags' selected within the Project Type column so that I can then do a count on the number of times that 'tag' has been selected?
Note: I'd only want a 'tag' to appear in the helper sheet once i.e. Wi-Fi (Upgrade) should only appear in the helper sheet 1 time, not twice. e.g.
The multi select drop down is not restricted to a defined list as we want to keep it dynamic based on needs. I want to avoid the admin overhead of hardcoding options if I can...
Probably worth noting we are only on the Pro plan at the moment...
Hope that all makes sense :)
Thanks,
Iain
Answers
-
Hi @IainC,
To my knowledge it is not possible to do this within the Free, Pro or Business plans.
Thanks,
John
-
Thanks for the reply John. I'll have to look at doing things a different way I guess. Cheers
-
Hi @IainC
One way to get a distinct list of all the options from your other sheet would be to use the JOIN function in your cross-sheet formula. It would join all the values into one cell, and there isn't a way to parse them down a column as individual cells... but it would give you one set, distinct list.
Try this formula in a multi-select cell in your second sheet:
=JOIN({Multi Select Column}, CHAR(10))
What you could then do is have each individual tag listed down this sheet in their own cells, and count the number of individually listed cells against the number of multi-select values in your cross-sheet formula.
You can reference the formula cell in a COUNTM function to see how many are selected:
=COUNTM([Join Formula Cell]@row)
Does that make sense? You'll need to manually add in the new values to new rows as they appear, but this would at least give you a heads-up when there is one.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks for your reply. I'll have a try at doing this. Being notified when something new is added would be a good compromise - hopefully it won't be too frequent an occurrence!
Thanks again, much appreciated.
Iain
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!