Find cells in column that contain 2 specific words

John Stanik

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

hollyconradsmith

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)

John Stanik

@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

hollyconradsmith

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)

John Stanik

@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