Finding and Counting Multi Select Items in a column

Options
Laura J
Laura J ✭✭✭✭
edited 10/19/22 in Formulas and Functions

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

  • MCorbin
    MCorbin Overachievers Alumni
    Options

    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.

  • Laura J
    Laura J ✭✭✭✭
    Options

    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.


  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!