Counting occurrences of Value in Multiple Selection with GoogleSheets

Question

I have a column, that contains multiple selection value like this

How to count occurrences of values in multiple selection with pivot table or gg sheets function?

I dont have any ideas for this problem. Can calculated by appscript?

Answer 1

You can use the QUERY function. Assuming your data is in A2:A6, the formula would be:

=ARRAYFORMULA(
   QUERY(
     TOCOL(SPLIT(A2:A6,", "),3),
     "select Col1, count(Col1)
      group by Col1
      label count(Col1) ''"))

This formula also accepts open ranges, like A2:A.

Answer 2

My understanding is that you want to find the count of values selected from the dropdown for a given cell.

You can do it using Google sheets function itself.

Let's say you want to find number of comma separated values in the cell A2. Use the below formula to do that.

=COUNTA(SPLIT(A2,","))

Answer 3

You will need two formulas for this:

1. Extract unique values:

   =sort(unique(flatten(map(B3:B5, lambda(v, if(isblank(v),, split(v, ", ")))))))
   

   Here B3:B5 is the range of the data with multiple selection. It sorts the values alphabetically and assumes that the separator is a comma followed by a space: , .

2. Count number of occurrences of the list of separate values in the data:

   =map(D3:D7, lambda(v, if(isblank(v),, counta(filter(B3:B5, regexmatch("^"&B3:B5&"$", v))))))
   

   Here D3:D7 is the rage of the values we generated in step 1, and B3:B5 is the original data with multiple selection. The formula assumes that within one selection each value appears only once.

This is how it looks in a spreadsheet: