Count Response Occurrence from Multiselect Drop Down

IainC · 01/15/24

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

John_Foster · 01/15/24

Hi @IainC,

To my knowledge it is not possible to do this within the Free, Pro or Business plans.

Thanks,

John

IainC · 01/16/24

https://community.smartsheet.com/discussion/comment/413505#Comment_413505

Thanks for the reply John. I'll have to look at doing things a different way I guess. Cheers

Genevieve P. · 01/16/24

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

IainC · 01/16/24

https://community.smartsheet.com/discussion/comment/413606#Comment_413606

Hi @Genevieve P.

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

Count Response Occurrence from Multiselect Drop Down

Answers

Help Article Resources

Categories