Find cells in column that contain 2 specific words
Hello Community,
I'm using a helper column formula and trying to identify cells in another column that contain the words "Basics" and "Clinical".
The column I'm referencing is "Event Name".
I tried this:
=IF(COUNTIFS([Event Name]:[Event Name], AND(FIND("Basics", @cell) = 1, FIND("Clinical", @cell) = 1)), 0, 1) and it gave me a "1" in all columns.
=IF(COUNTIFS([Event Name]:[Event Name], AND(FIND("Basics", @cell) = 0, FIND("Clinical", @cell) = 0)), 0, 1) and it gave me "Invalid Data Type".
Any suggestions?
Thanks in advance!
John
Best Answers
-
Hi @John Stanik ,
What is the output you are looking for? Is the helper a checkbox, thus the 1, 0 output of the IF() formula?
FIND returns the starting position of the text you are searching for. Are you trying to discover if the range has cells that START with the text you are searching for?
CONTAINS returns true/false whether the text exists, regardless of position within the cell.
If you are trying to count the number of times Basics or Clinical appear in the range, maybe the Contains formula would work better for this?
=COUNTIF([Event Name]:[Event Name], AND(CONTAINS("Basics", @cell), CONTAINS("Clinical", @cell)))
If your desired output is a checkbox, this might work:
=IF(AND(CONTAINS("Basics", [Event Name]:[Event Name]), CONTAINS("Clinical", [Event Name]:[Event Name])), 1, 0)
Holly Conrad Smith
Director of Technology & Innovation 💡 at Streamline
CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony
-
Hi Holly,
By playing with your solution I was able to come up with:
=IF(COUNTIFS(CHILDREN([Event Name]@row), CONTAINS("Basics", @cell), CHILDREN([Event Name]@row), CONTAINS("Clinical", @cell)) > 0, 1)
Which is exactly what I needed. Thanks so much for your help!!!!
Best,
John
Answers
-
Hi @John Stanik ,
What is the output you are looking for? Is the helper a checkbox, thus the 1, 0 output of the IF() formula?
FIND returns the starting position of the text you are searching for. Are you trying to discover if the range has cells that START with the text you are searching for?
CONTAINS returns true/false whether the text exists, regardless of position within the cell.
If you are trying to count the number of times Basics or Clinical appear in the range, maybe the Contains formula would work better for this?
=COUNTIF([Event Name]:[Event Name], AND(CONTAINS("Basics", @cell), CONTAINS("Clinical", @cell)))
If your desired output is a checkbox, this might work:
=IF(AND(CONTAINS("Basics", [Event Name]:[Event Name]), CONTAINS("Clinical", [Event Name]:[Event Name])), 1, 0)
Holly Conrad Smith
Director of Technology & Innovation 💡 at Streamline
CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony
-
Hi Holly,
By playing with your solution I was able to come up with:
=IF(COUNTIFS(CHILDREN([Event Name]@row), CONTAINS("Basics", @cell), CHILDREN([Event Name]@row), CONTAINS("Clinical", @cell)) > 0, 1)
Which is exactly what I needed. Thanks so much for your help!!!!
Best,
John
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 359 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 135 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!