I am trying to count the number of occurrence on a cell but it doesn't seem to work.So for instance i have dumy_data sheet which contains the dummy data that i want to count. On another sheet test is where i want to show to results. so i did something like this ={COUNTIF(SPLIT(dumy_data!$A$2:$A$13,","),A2)} but i get zero as the number of occurrence for lucy ken. The result suppose to be 2 as the number of occurrence not 0. Also number of occurrence of Ben should be 3

CodePudding user response：

try:

=INDEX(QUERY(FLATTEN(SPLIT(TEXTJOIN(",", 1, dumy_data!A2:A), ",")), 
 "select Col1,count(Col1) group by Col1 label count(Col1)''"))