Contains formula not picking up text

Liam Burnett

Hi,

I have this contains formula that searches for M&E and im not sure why its not working.

Could someone please point me to where im going wrong.

Thanks,

Liam

Paul Newcome

The problem is with the range portion of the CONTAINS function. Try using "@cell" exactly as I have it.

=COUNTIFS([Primary Column]1:[Primary Column]5, CONTAINS("M&E", @cell))

@cell tells the formula to look across the previously specified range and evaluate on a cell by cell basis. When you specify "[Primary Column]1" in that portion, you are telling the CONTAINS function to only look at [Primary Column]1 instead of the entire range already established.

Paul Newcome

Try this instead:

=COUNTIFS([Primary Column]1:[Primary Column]5, CONTAINS("M&E", @cell))

Liam Burnett

Hi Paul,

Unfortunately that didn't work for me.

Liam

Paul Newcome

I tested, and it worked for me. Can you provide the EXACT formula that shows what you updated?

Liam Burnett

=COUNTIFS([Primary Column]1:[Primary Column]5, CONTAINS("M&E", [Primary Column]1))

I used the one you provided.

Paul Newcome

The problem is with the range portion of the CONTAINS function. Try using "@cell" exactly as I have it.

=COUNTIFS([Primary Column]1:[Primary Column]5, CONTAINS("M&E", @cell))

@cell tells the formula to look across the previously specified range and evaluate on a cell by cell basis. When you specify "[Primary Column]1" in that portion, you are telling the CONTAINS function to only look at [Primary Column]1 instead of the entire range already established.

Liam Burnett

Ah yeah, thank you for your help, this works great :)

Paul Newcome

Happy to help! 👍️

Please don't forget to flag the most appropriate response(s) as "helpful". This way others looking for a similar solution can know that one may be found here.

Liam Burnett

I have accepted the answer and clicked on the insightful option but there is no "helpful" option when i click on the flag.

Paul Newcome

It may have been updated. It used to be that it asked if the question was "helpful" and that is what flagged it as "Accepted". I had typed that up before I got the notification that you had accepted the answer, so you ended up getting it anyway. Haha.

Either way... Feel free to come back to the Community if you have anymore questions!

Liam Burnett

Haha, no worries, thanks will do :)

MHumphrey

I have a similar issue that the fixes described above don't quite cover. I have a dataset with 90+ values that I need to address in the same way as Liam. However, I would like to replace the 'searchable value' described in quotation marks with something more dynamic so I don't have to type 90+ values. I have tried using CONTAINS([Column]@row,@cell), CONTAINS([Column]@row,Range], HAS([Column]@row@cell), and HAS([Column]@row,Range). How do I make this formula dynamic?

I may not be understanding the CONTAINS and HAS formulas fully—please let me know if it's a limitation of these formulas and what the best solution would be. This is my current formula:

=SUMIFS({Contract Value}, {Contractor}, CONTAINS([Contractor]@row,@cell), {Project Status}, OR(@cell = "Portal Pricing Needed", @cell = "Portal Pricing Complete", @cell = "Proposal Submitted", @cell = "Shop Drawings Needed", @cell = "Specialties Needed", @cell = "On Order", @cell = "Specialties Complete")))