Hi There, I have a column with comma separated strings. How to find the occurrence of each string?

Please see the below table. I have 20,000 responses. I need to find out how many times each response was given.

I have 20000 responses. I need to find out how many times each response was given.

Thank You.



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Is there a possibility that a response could be duplicated within the same cell?

    For example...

    agree, agree, disagree, rather agree

  • No, responses could not be duplicated within the same cell.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Galla Ramulu

    Since your values contain the same phrasing (ex. the word "agree" appears within the word "disagree") it makes counting each individual instance a little trickier.

    I would add a helper column next to this column to evaluate the values on a cell-by-cell basis, returning each of the values that appear within commas as an individual value in a multi-select column, like so:

    =SUBSTITUTE(String@row, ",", CHAR(10))

    Now that your values are in a Multi-Select column, we can use the HAS function to see how many times a unique value is in this column, like so:

    =COUNTIF([Multi-Select Column]:[Multi-Select Column], HAS(@cell, "agree"))

    The HAS function makes sure that it doesn't count the "agree" in "disagree", but searches for the exact selection instead.

    Will this work for you?



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Galla Ramulu My apologies. I missed the alert from your reply. I would make the same suggestion as Genevieve above.

    @Genevieve P. Thanks for stepping in!

  • Genevieve P.
    Genevieve P. Employee Admin

    Always good to know I'm on the right track!! Thanks Paul 🙂

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Genevieve P. That's why I asked if there could be duplicates within the same cell. I wanted to use this method with the multi-select and HAS functions, but if the same response could be duplicated within a single cell then it would not work as the multi-select would filter out the duplicate and you wouldn't get an accurate count.

    @Galla Ramulu One thing to note... I just noticed in your screenshot there are a couple of entries that do not have a comma separating them. This will skew the end results. The only place I noticed this in your screenshot is when the last two entries are "rather agree" and "moderate" in that order. Those few rows have

    "rather agree moderate"

    instead of

    "rather agree, moderate"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!