google-sheetsarray-formulascountif

How to count a specific word from a string in a cell from the entire column in google sheets

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

Sample

I tried COUNTIF(A1:A10,"Apple") am getting the result as 1, but am expecting the answer as 6 Apple.

Solution

  • If you just want to count the appearances, you can use COUNTIF with wildcards, like this:

    =COUNTIF(A:A,"*"&C1&"*")

    enter image description here