Find cells in column that contain 2 specific words

Options

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

  • hollyconradsmith
    hollyconradsmith ✭✭✭✭
    Answer ✓
    Options

    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

  • John Stanik
    John Stanik ✭✭
    Answer ✓
    Options

    @hollyconradsmith

    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

  • hollyconradsmith
    hollyconradsmith ✭✭✭✭
    Answer ✓
    Options

    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

  • John Stanik
    John Stanik ✭✭
    Answer ✓
    Options

    @hollyconradsmith

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!