Finding and Counting Multi Select Items in a column
I have a column of data (multi-select) to tag like projects called Tag Name. I want a formula to find each unique Tag Name and display the Tag Name and the Count (number of times that tag name appears). Thanks
Answers
-
I will usually do this kind of thing in a separate metrics sheet:
Formula: =COUNTIF({Tags}, FIND([Tag Name]@row, @cell) > 0)
Where {Tags} is a cross-sheet reference to the multi-select column in your data sheet with the tag names.
-
I hope this image of my master project sheet and metadata sheet (with expected results) will help me explain better. I have a master project sheet with a column for collecting tags (to identify related projects). I want to dynamically find and list the tag names, on a metadata sheet, in a Tag Names column. I want to dynamically count the number of times the tag is used, and list on the metadata sheet, next to the tag name. The number of projects will always increase. The tag names will continue to increase, so this has to be a dynamic solution. Here is my master project sheet and my expected result on a metadata sheet. I need the formulas to get these expected results.
-
Hi @Laura J
There currently isn't a direct way to reference an entire multi-select column from one sheet and parse out all the unique options selected down multiple rows in another sheet.
You could use a JOIN( formula to bring together all the unique multi-select values into one cell, then Count the values in that cell and compare it to your Tag Name list row count:
This wouldn't tell you which tags are missing, but it would alert you to new Tags that have been created and are not yet a part of your Tag Name column.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!