# counting multiple instances of one keyword in one cell

edited 06/24/20

I'm trying to count the number of instances of a keyword in one cell (for instance if I have "Green Apples; Yellow Bananas; Brown Bananas; Reg Apples" in my Products column for this row, I want to count the number of times "Apples" appears in the Products cell for this row, which should be 2).

I'm using this formula:

=COUNTIF(Products1:Products1, FIND("Apples", @cell) > 0)

And it's giving me a count of 1 regardless of how many instances of the keyword "Apples" I have in my Products cell. (I suspect "1" means it counted 1 cell with the word, so I think I know what I'm getting, but I don't know how to get what I actually want!)

• ✭✭✭✭✭✭
edited 06/24/20

How many items would be listed in a single cell?

• @RossL for now there are 5-6, but it will continue to grow up to maybe 20 over time

• ✭✭✭✭✭✭
edited 06/25/20

Try something like this. Update so it matches your column names/ranges.

```=COUNTIFS([Multi select]:[Multi select], HAS(@cell, Fruit@row))

=COUNTIFS([Products1]:[Products1], HAS(@cell, "Apples"))
```

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Thanks, but that didn't quite work, I get a count of zero with that formula.

I mocked up a sample sheet in case that helps clarify what I'm trying to do...in the regular sheet, the Product column is just a text input column, not a multi-select dropdown column, but I can convert to mutli select. However, even with the formulas suggested above targeting a mutli select column, I get wonky results in the count column.

https://app.smartsheet.com/b/publish?EQBCT=d88df91feafb4fee93971e1151cc5e7b

• ✭✭✭✭✭✭

I came up with a solution, it is not the most elegant but it works. below is a link to the sheet I made. what it is doing is looking for the key word and using helper rows to find the start of that word and then the next column in the line will find the next one by start its search after the first word, then the start of the next occurrence ect. the green column counts all the columns that have a position noted.

https://app.smartsheet.com/b/publish?EQBCT=78c55d4176554b85afc0ef5bf456f4b1

• ✭✭✭✭✭✭

Did you get it working?

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!