Count if *specific word* is present, alone or among other words.

Colleen L.

 Hi all. Looking for help with a formula where the excel method does not work in Smartsheet.

I want to count the number of occurrences of a word where sometimes that word has other words in the cell. I need SmartSheet to count the word whether or not the word is in a cell by itself or among other words. I am adding this formula to a blank sheet, pulling the data from another sheet.

Example: a list of clients and their locations and associated data is the source sheet (multiple clients/sites/lines per client). On a separate sheet, I want to add a formula to summarize data from the source sheet. I want to know how many occurrences there are of the word "Office" in the Building Type column per client in the source sheet, and some cells have multiple building types.

Right now, my formula will only count Office when it is the only word in a cell.  

Thank you

ker9

Hi @Colleen L.

Please see if this works for you - presuming you have a base sheet similar to this:

And your summary sheet looks similar to this:

Update the bolded reference in the formula to reference your base sheet

=COUNTIFS({Test Contains BldgType}, CONTAINS([Building Types]@row, @cell))

Hope this helps!