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.

Find more posts tagged with

Formulas

Comments

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

mjensen

That worked, thank you so much!

Andrée Starå

Great!

Happy to help!

Best,

Andrée

Sign in to join the conversation!

Quick Links

Trending discussions

How to use previous cell value (from cell history) in a formula?
I have seen this question asked multiple times in the forum, but I couldn't find any clear answer provided in the answers. Please excuse me if a solution already exists, but if not, will someone please let me know if it is possible to use a previous cell value (from cell history) in a formula? I am specifically trying to…
Automation not triggering when % Complete shows '100% instead of 100%
I have an automation set up so that when the Status column changes to Complete, Canceled, or Not Needed, Smartsheet automatically updates the % Complete column to 100% (Text/Number column type). After that, another workflow should trigger to alert the next person that their task is ready. This worked fine before, but…
Formula for populating date
I am needing a formula that calculates Termination Date (date property). The formula needs to look at Initiation Date (date property) and Term (text/num) which is based on # months, to calculate Termination Date. In this example, the termination date should return a value of 1/01/26. Thank you!