Need Formula to capture all instances of text alone or with other text in a column

Delilah

Currently using this formula to count all instances of a matching status with EBIT division. Sometimes EBIT is not the only division listed in the column. So a cell might have EBIT TBI. This formula works only if EBIT is alone.

=COUNTIFS({Status Phase}, [Primary Column]@row, {Sponsoring Division}, "EBIT")

I tried adding both CONTAINS and HAS in front of EBIT but both return invalid operations.

can anyone help?

Paul Newcome

Is the division column a multi-select dropdown?

Delilah

Yes but I have hidden column that copies it as text. Do I need to use the text column? And do I have to unhide it? Or is there a formula to use the original column.

Paul Newcome

You would use the HAS function with the original column.

=COUNTIFS(……………………, HAS(@cell, "EBIT"))

Delilah

That worked. I needed to add the @cell. Thanks very much!

Paul Newcome

Happy to help. 👍️