Count occurences in multiple selection list

[ML2021]

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

[#2 · Accepted solution]

@ML2021 

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

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

[#3]

thanks so much! @Hans Vogelaar