counting multiple instances of one keyword in one cell

Shannon Petersen

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!)

Any advice?

RossL

@Shannon Peterson

How many items would be listed in a single cell?

Shannon Petersen

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

Andrée Starå

Hi @Shannon Petersen

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

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

Added your exmaple.
=COUNTIFS([Products1]:[Products1], HAS(@cell, "Apples"))

https://help.smartsheet.com/function/has?dlm=112&frame=0&nav=1

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Shannon Petersen

Hi @Andrée Starå ,

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

RossL

@Shannon Petersen

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

Andrée Starå

@Shannon Petersen

I missed your answer.

Did you get it working?