Formula to count instances of a word

Formula to count instances of a word

I have a sheet that is referencing another sheet to provide counts on instances of certain values in different columns. 

What I'm stuck on is counting how many times a single word is used if other text is in the same cell.

For example if I have a column call Fruit and I want to know how many cells contain the word "apple" or "orange" but the cells might have red apple, green apple, granny smith apple, etc... I just want to know how many apples there are without having to have a formula for every variety of apple.

 

Tagged:

Comments

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi,

    Try this.

    This formula will count all cells containing the word "Apple"

    =COUNTIF(Fruit:Fruit; FIND("Apple"; @cell) > 0)    

    The same version but with the below changes for your and others convenience.  

    =COUNTIF(Fruit:Fruit, FIND("Apple", @cell) > 0)

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    I hope this helps you!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • That worked, thank you so much!

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Great!

    Happy to help!

    Best,

    Andrée

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

Sign In or Register to comment.