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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!