Formula help recognising multiple words
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
Comments
-
Hi Dave,
Eid's formula will work. (Nicely done Eid)
To add. I've added @row and also the structure for different regions.
Try something like this.
=COUNTIFS(Response@row; OR(FIND("shower"; LOWER(@cell)) > 0; FIND("clean"; LOWER(@cell)) > 0))
The same version but with the below changes for your and others convenience.
=COUNTIFS(Response@row, OR(FIND("shower", LOWER(@cell)) > 0, FIND("clean", LOWER(@cell)) > 0))
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.
I hope that helps!
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thank you both for taking the time to help.
Once again the community proves invaluable.
Regards
Dave
-
You can also use CONTAINS instead of FIND which removes the need for UPPER vs LOWER functions since CONTAINS is not case sensitive.
-
Happy to help!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
That's a good point for using CONTAINS because of it not being case sensitive.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Really it depends on the use case. I use still use the FIND function sometimes because I CAN include case sensitivity if needed and the CONTAINS function (as I explained above) lets you cut out UPPER and LOWER functions.
Overall, the CONTAINS function has been a very excellent addition.
-
Yes, it depends on the use case. I can't wait for more formulas to get released.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!