COUNTIF Formula for when a cell contains a specific word/phrase?

Briana White

I'm trying to create a formula that allows me to count the number of times the word "Postcard", for example, appears in a list in a column. I am referencing a column in another sheet to do so. However, nothing I've tried works. 

My instinct was to try =COUNTIF({2018 Open Enrollment Feedback Range 1}, "Postcard"). However, this only returns a value if the cell ONLY contains the word "Postcard". I am trying to create a formula that counts every single time a cell in a column contained the word Postcard, instead. 

Individuals were asked to select from a list of 11 options all that applied to them. I attempted making a formula that included a unique COUNTIF formula for each option that could be in the cell, but after just a couple of different possibilities, it became excessive. A single cell could potentially include over 124 characters if all options are selected and the "Other" option filled out. I'm not sure if there is a formula I can use to search the cells and then count the occurrences or what. At this point, I'm open to anything not too excessive.

I've included a screenshot of the column I'm trying to count occurrences in and a sheet showing which things I'm trying to count to get a better idea of what I'm doing. 

Any suggestions? I need this data to show on a dashboard by tomorrow morning.

Thanks!

Mike Wilday

You'll want to combine a FIND function with the countif.

Try: 

=COUNTIF({2018 Open Enrollment Feedback Range 1}, Find("Postcard",{2018 Open Enrolllment Feedback Range 1}) >0)

https://help.smartsheet.com/function/find

Basically you're combining the find function as your criteria. You might have to add @cell to the end of the range range... but basically, create teh Find function within it, reselect the same range of text and have it search it for "Postcard" and count it. 

Mike Wilday

Sorry, my answer was innacurate. This what the formula should look like.. and you don't have to reselect the range. Use @cell in the Find formula for the range it is looking for. 

Try this formula. You might have to reselect your cross sheet reference but I am not sure.

=COUNTIF({2018 Open Enrollment Feedback Range 1}, Find("Postcard",@cell) >0)