CONTAINS: What's it all about and how does it work?

JGEROLD

@Paul Newcome I am trying to use the count function to total a status including "PASS" but I am messing up the syntax. I am referencing another sheet:

=COUNTIF({(0) AMPF - Compliance Dashboard: 2021 Range 2}, CONTAINS = "PASS")

I noticed you are killing it with suggestions and i have questions :-)

Paul Newcome

@JGEROLD Try this...

=COUNTIF({(0) AMPF - Compliance Dashboard: 2021 Range 2}, CONTAINS("PASS", @cell))

Terri

@Paul Newcome need your expertise, please!

I need help with a CONTAINS formula, which I think is the formula I need to use. I have 2 columns ... Prob List Status and Allergy/Med Status. Each column contains the same 4 drop down selections ... In Process, In Process (APP), Completed, Completed (APP). The 3rd column is Overall Status, which has my formula in it.

If the status in Prob List Status is selected as In Process and the Allergy/Med Status is selected as Completed, the Overall Status would show as In Process. If both columns have either In Process selected, the Overall Status would show as In Process. And if both columns have either Completed selected, the Overall Status would show as Complete.

The problem is if the first column, Prob List Status is blank and the 2nd column, Allergy/Med Status is Completed, the Overall Status returns Complete and I want it to return In Process. Below is the formula I'm using and I have tried putting in "" at the beginning of the argument and end of the argument, but not having any luck. Can you help me out, please?

=IF(CONTAINS("In Process", [Prob List Status]@row:[Allergy/Med Status]@row), "In Process", IF(CONTAINS("Completed", [Prob List Status]@row:[Allergy/Med Status]@row), "Complete"))

Thanks,

Terri

Jeff Reisman

@Terri It's just a matter of the logic structure, and adding in some ANDs and ORs since you have multiple criteria. Try something like this:

=IF(AND(CONTAINS("Complete", [Prob List Status]@row), CONTAINS("Complete", [Allergy/Med Status]@row)), "Complete", IF(AND([Prob List Status]@row = "", [Allergy/Med Status]@row = ""), "", IF(OR(CONTAINS("In Process", [Prob List Status]@row:[Allergy/Med Status]@row), ISBLANK([Prob List Status]@row), ISBLANK([Allergy/Med Status]@row)), "In Process")))

In English: If both columns contain the string "Complete", set the status to "Complete"; If both columns are blank, set the status to blank; If either column contains "In Process" or is blank, set the status to "In Process".

Keep in mind, nested IFs work from left to right. As soon as a true condition is found, it applies the true condition value and stops considering anything else. So if both contain "Complete", it sets status to Complete and it's done. If not, it goes on to the next thing; are they both blank? Yes, leave it blank, no, then continue on to see if either contain In Process or if one or the other is blank, and if so, set the status to "In Process."

Terri

@Jeff Reisman - thank you for the quick response and the formula. I tried it out on my sheet, but unfortunately it is not giving me the results I'm looking for.

For example, if any of the Completed selections are selected in the Prob List Status column and nothing has been selected in the Allergy/Med Status column, the Overall Status should show as In Process, but instead it is showing up blank and that's not what I want.

The same thing is true if nothing is selected in the Prob List Status column and any of the Completed selections are selected in the Allergy/Med Status column, the Overall Status should show as In Process. It should not be blank.

The Overall Status would only be blank if nothing in Prob List Status and Allergy/Med Status has been selected.

Thanks,

Terri

Terri

@Jeff Reisman - I got it!! I added another nested IF to your formula and it works now!

=IF(AND(CONTAINS("Complete", [Prob List Status]@row), CONTAINS("Complete", [Allergy/Med Status]@row)), "Complete", IF(AND([Prob List Status]@row = "", [Allergy/Med Status]@row = "Completed"), "In Process", IF(AND([Prob List Status]@row = "Completed", [Allergy/Med Status]@row = ""), "In Process", IF(CONTAINS("In Process", [Prob List Status]@row:[Allergy/Med Status]@row), "In Process"))))

Thank you again for your help!!

Terri

Terri

UPDATE:

So, I realized after I added the formula that it was still missing another IF(AND statement to pick up if the Prob List Status column was blank and the Allergy/Med Status = "Completed (APP)", then it returns "In Process".

Oh and then the owners of the original sheet made 2 changes to the Prob List Status column, by changing the wording for one and removing 2 of the drop downs and that threw my formula out of whack. I finally got the results I needed and just wanted to share here in case it helps others too. I have found the Smartsheet Community forums to be extremely helpful when looking for particular formulas I need to use.

=IF(AND(CONTAINS("Complete", [Prob List Status]@row), CONTAINS("Complete", [Allergy/Med Status]@row)), "Complete", IF(AND([Prob List Status]@row = "", [Allergy/Med Status]@row = "Completed"), "In Process", IF(AND([Prob List Status]@row = "", [Allergy/Med Status]@row = "Completed (APP)"), "In Process", IF(AND([Prob List Status]@row = "Complete", [Allergy/Med Status]@row = ""), "In Process", IF(CONTAINS("In Process", [Prob List Status]@row:[Allergy/Med Status]@row), "In Process")))))

Terri