Formula help recognising multiple words

dave.mcpherson56751
dave.mcpherson56751 ✭✭✭✭✭
edited 12/09/19 inFormulas and Functions

Hi,

I have a formula that looks for a specific word or variance of the word, is it possible to look for more than one word?

The formula i have is =COUNTIFS(Response1, FIND("shower", LOWER(@cell))> 0)

This works for the word showers or shower which is perfect, however am i am able to count if the cell contains either "shower" or "Clean"

Appreciate any help or guidance.

As always thank you in advance

Dave

Tags:
· Share on Facebook Share on Twitter

Comments

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册template!
@J.Sanabria<\/a> In the notification set up, under Message includes: you would have to select Message only.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":112203,"type":"question","name":"Help w\/ Sumif date is before today","excerpt":"Hi, Hitting a snag w\/ a very basic sumif. I need to sum the total capital investment column if it's corresponding date value falls before or on today's date. Here's the formula I have now: =SUMIF([Fiscal Month End]1:[Fiscal Month End]12, <=TODAY(), [Actual Capital Investment]1:[Actual Capital Investment]12) And a…","snippet":"Hi, Hitting a snag w\/ a very basic sumif. I need to sum the total capital investment column if it's corresponding date value falls before or on today's date. Here's the formula I…","categoryID":322,"dateInserted":"2023-10-26T14:19:00+00:00","dateUpdated":null,"dateLastComment":"2023-10-26T15:04:11+00:00","insertUserID":130047,"insertUser":{"userID":130047,"name":"Alex Hackford","url":"https:\/\/community.smartsheet.com\/profile\/Alex%20Hackford","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m_jVn4tpYLQ!zh1V8wNL1sQ!ziK_jPs8qoT","dateLastActive":"2023-10-26T16:09:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":130047,"lastUser":{"userID":130047,"name":"Alex Hackford","url":"https:\/\/community.smartsheet.com\/profile\/Alex%20Hackford","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m_jVn4tpYLQ!zh1V8wNL1sQ!ziK_jPs8qoT","dateLastActive":"2023-10-26T16:09:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":14,"score":null,"hot":3396663791,"url":"https:\/\/community.smartsheet.com\/discussion\/112203\/help-w-sumif-date-is-before-today","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112203\/help-w-sumif-date-is-before-today","format":"Rich","lastPost":{"discussionID":112203,"commentID":401837,"name":"Re: Help w\/ Sumif date is before today","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401837#Comment_401837","dateInserted":"2023-10-26T15:04:11+00:00","insertUserID":130047,"insertUser":{"userID":130047,"name":"Alex Hackford","url":"https:\/\/community.smartsheet.com\/profile\/Alex%20Hackford","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m_jVn4tpYLQ!zh1V8wNL1sQ!ziK_jPs8qoT","dateLastActive":"2023-10-26T16:09:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-26T15:04:18+00:00","dateAnswered":"2023-10-26T14:24:37+00:00","acceptedAnswers":[{"commentID":401817,"body":"

Your formula looks good! But is your \"Fiscal Month End\" formatted as a date column? It looks like a text (should have the leading 0's for the months). Double click on the header to change it to a Date type column and you should be good to go!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":112198,"type":"question","name":"How to count one value or combo of values in a column of multiple values","excerpt":"Hi, we have a multi-select dropdown list column, where users chose one or more options. We want to count how many times a user chose an option (or combination of options), or did not choose a specified option. We can count all instances when one particular option was chosen using the following formula, but we cannot figure…","snippet":"Hi, we have a multi-select dropdown list column, where users chose one or more options. We want to count how many times a user chose an option (or combination of options), or did…","categoryID":322,"dateInserted":"2023-10-26T13:14:00+00:00","dateUpdated":null,"dateLastComment":"2023-10-26T14:57:06+00:00","insertUserID":119221,"insertUser":{"userID":119221,"name":"Mike Beam","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Beam","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-26T14:35:03+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-26T16:05:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":34,"score":null,"hot":3396661266,"url":"https:\/\/community.smartsheet.com\/discussion\/112198\/how-to-count-one-value-or-combo-of-values-in-a-column-of-multiple-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112198\/how-to-count-one-value-or-combo-of-values-in-a-column-of-multiple-values","format":"Rich","lastPost":{"discussionID":112198,"commentID":401833,"name":"Re: How to count one value or combo of values in a column of multiple values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401833#Comment_401833","dateInserted":"2023-10-26T14:57:06+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-26T16:05:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-26T14:35:01+00:00","dateAnswered":"2023-10-26T13:50:38+00:00","acceptedAnswers":[{"commentID":401805,"body":"

For Goal 1:<\/p>

=COUNTIFS(Dropdown:Dropdown, COUNTM(@cell) = 1, Dropdown:Dropdown, HAS(@cell, \"Option Name\"))<\/p>

For Goal 2 (with 2 options):<\/p>

=COUNTIFS(Dropdown:Dropdown, COUNTM(@cell) = 2, Dropdown:Dropdown, AND(HAS(@cell, \"Option Name 1\"), HAS(@cell, \"Option Name 2\")))<\/p>

If your options are numbers, you can leave out the quotation marks. <\/p>

If you are wanting to have more than 2 options, increase the COUNTM value and add the extra HAS(@cell, \"Option Name X\") within the AND function.<\/p>

Hope this helps, but I've misunderstood something or have any problems\/questions then just post them up! 🙂<\/span><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • Automation Alert Row Info

    Hi, I'm working in a workflow a I setup an alert. I run it and I notice that below my custom message, is all the information of the row that I modify. Is it possible to disable the information of the row being sent?

  • Help w/ Sumif date is before today

    Hi, Hitting a snag w/ a very basic sumif. I need to sum the total capital investment column if it's corresponding date value falls before or on today's date. Here's the formula I have now: =SUMIF([Fiscal Month End]1:[Fiscal Month End]12, <=TODAY(), [Actual Capital Investment]1:[Actual Capital Investment]12) And a…

  • How to count one value or combo of values in a column of multiple values

    Hi, we have a multi-select dropdown list column, where users chose one or more options. We want to count how many times a user chose an option (or combination of options), or did not choose a specified option. We can count all instances when one particular option was chosen using the following formula, but we cannot figure…