I have a data in a column like the name of Fruits with comma separate, to which I want to find a count of each fruit occurrence in the cells.
Sample data the entire data is in one column.
Fruits
Apple
Apple, Mango, Banana
Mango, Grapes
Mango
Grapes, Apple
Banana, Mango, Apple
Papaya, Mango, Apple, Grapes
Banana, Papaya, Apple
Banana
I tried COUNTIF(A1:A10,"Apple") am getting the result as 1, but am expecting the answer as 6 Apple.
Mujassim is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
If you just want to count the appearances, you can use COUNTIF with wildcards, like this:
=COUNTIF(A:A,"*"&C1&"*")
1
Use Split() and Join() Functions
It reconstructs all the fruits on column A as individual Array per element and then the countif function counts all the occurrences of the fruits.
You may try this:
={unique(transpose(split(substitute(join(",",$A$2:$A$10)," ", ""),","))),arrayformula(countif(split(substitute(join(",",$A$2:$A$10)," ", ""),","),unique(transpose(split(substitute(join(",",$A$2:$A$10)," ", ""),",")))))}
Sample Output:
Reference:
Google Sheets Function List
1
An alternative using QUERY where the formula in cell E1 is;
=QUERY(TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("|",,A1:A),",", "|")," ",""),"|")),"Select Col1, Count(Col1) Group By Col1 Label Count(Col1) ''")
.
2