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.
Is there a possibility that a response could be duplicated within the same cell?
agree, agree, disagree, rather agree
No, responses could not be duplicated within the same cell.
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([email protected], ",", 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?
@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!
Always good to know I'm on the right track!! Thanks Paul 🙂
@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"
"rather agree, moderate"