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

Galla Ramulu

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

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

For example...

agree, agree, disagree, rather agree

Galla Ramulu

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

Genevieve P.

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?

Cheers,

Genevieve

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.

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

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"

kdrinkwater

I'm trying to use this solution in my own sheet. The SUBSTITUTE piece worked perfectly and I have my multi-select row set up but this formula suggested above simply will not work.

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

=IF(HAS(Multi-Select Column@row, "agree") = 1, 1, 0) works fine so I can count when this occurs for the cell in the row, but when I try to expand to the column and total the instances in the column the formula returns zero.

Has something changed with this function?

KPH

Hi @kdrinkwater

The function still works.

Is this exactly what is in your cell?

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

If so, there is a missing square bracket (shown in bold):

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

Hopefully that is all it is. 🤞

kdrinkwater

Hi @KPH -

I believe my problem is that I was trying to use HAS in a cross sheet formula.

So technically the formula I posted above wasn't (even with square bracket typo resolved) wasn't quite right.

I couldn't find/possibly overlooked indication on the HAS Function | Smartsheet Learning Center page that calls out that this function cannot be used in a cross sheet formula.

My use case was in a metric sheet where I wanted to take this example a step further and not just count the occurrances of "agree" @row, but count the occurrances of "agree" in the multiselect column for the entire sheet.

Edited to add: I ended up creating helper columns with check boxes to use HAS on every row for all iterations of my selection, then I counted those in my metric sheet. Just makes the sheet a lot messier and the metric sheet isn't as tidy since all the references are now hard coded instead of dynamic.

KPH

Hi @kdrinkwater

If your data sheet is a multiselect like this:

You can use a COUNTIF cross sheet formula like this:

Paul Newcome

@kdrinkwater The HAS function can most certainly be used with cross sheet references. Are you able to provide a screenshot of the formula you are using actually open in the sheet as if you are about to edit it?

kdrinkwater

Thank you @KPH for sharing the formula with a cross-cell example and @Paul Newcome for confirming this was possible. Now to go clean up the solution and get rid of the helper columns!

My mistake was instead of putting "@cell" in the first HAS argument, I was referencing {Source Sheet - MultiDrodown} there. This is the first time I've had a use case for @cell, I've always used @row, and since the formula helper didn't highlight it I never realized it was actually a keyword. I've honestly always thought that when I've seen "@cell" in these forms, it was just being used as a placeholder. 😅

Here is a screenshot of the formula in the cross-sheet reference use case that I had. I used a COUNTIFS because I also needed to handle a DateDiff formula that I had in my source sheet (used to keep dashboard reports static).

Here is the source sheet, for reference:

KPH

Looks like you got there in the end @kdrinkwater 😍

Well done.