Get the count or sum of count of each individual items used in multi-select dropdown in google sheet-CodePudding

I have created a multi-select drop-down of items a,b,c, & d. Let's say I enter a,b in C1 and a,b,c in C2 and a,b,c,d in C3. Now, I want to get the total count of each item I have selected. Here, we know I have selected "a" thrice i.e (once in C1, once in C2 & once in C3). Similarly, I have selected "b" thrice as well. "c" twice (once in C2, once in C3) and "d" once (in C3). Can anyone suggest a workaround for this? Either in pivot or through a formula.

CodePudding user response：

You can try:

Formula in D1:

=QUERY(FLATTEN(SPLIT(TEXTJOIN(",",1,C1:C3),",")),"Select Col1, Count(Col1) Group By Col1 label Count(Col1) ''")

CodePudding user response：

You can try QUERY() function-

=QUERY(FLATTEN(ArrayFormula(SPLIT(C1:C3,","))),"select Col1, count(Col1) where Col1 is not null group by Col1 label count(Col1) ''")

CodePudding user response：

So just using countif() and you can do data validation but I just had a to d:

function:

=COUNTIF($C$1:$C$3,"*"&D1&"*")

With find() etc:

=SUM(IF(IFERROR(FIND(D1,C$1:C$3,1),0)>=1,1,0))

Note I used Excel as Excel was originally tagged.