Forum Discussion

ML2021's avatar
ML2021
Copper Contributor
May 12, 2021
Solved

Count occurences in multiple selection list

Hi,

 

I am looking for the most efficient way to count the number of times a string occurs in a dataset, depending on the strings in another column.

 

For context, this is a survey style data set where people have to chose their location, as well as tick a multiple selection list of their interests. The output groups the interests into a single cell separated by commas. I was wondering if there is a quicker way to analyse this other than splitting into multiple columns.

 

For example, with the below dataset, I want to count the number of times "Skincare" has been selected by respondents who have selected that they are based in the "UK" in the first column. At the moment, if I put into a pivot table, it just groups all of the multiple selection combinations as one, rather than the number of times the value has actually been selected.

 

Thank you

 

 

 

  • ML2021 

    Let's say location is in column A and products in column F.

     

    =COUNTIFS(A:A,"UK",F:F,"*Skincare*")

Resources