COUNTIF Formula for when a cell contains a specific word/phrase?
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!
 Sales
 Human Resources
 Manufacturing
 Search
 Nonprofit
 Construction
 Sheets
 Hospitality
 Healthcare
 Finance
 Education
 Formulas
 Government
 Real Estate
 Services
 Legal
 Marketing
 Support
 IT & Operations
 Product Development
 Project Management
 Update Requests
 Cell linking
 Media & Entertainment
 Enhancement Request
Comments

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.

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)
Help Article Resources
Categories
Check out the Formula Handbook template!