IF statement, multiple criteria, some with CONTAINS and some w/o

Dana G

Hello Community! First time poster here. I maintain an extensive tracking spreadsheet and would like one column to populate "Yes" if there are ANY issues in other columns.

The criteria is:

-- if any of these columns contain "TBD" (Lead, [Due Date], [Submission Date], [Other information], [QA Team Notes])

-- if any of these columns are NOT blank (Escalation, [Escalation Notes])

-- if [Submission Status] is one of “Submitted, pending Follow-up” or “Pending Request” or “Requested?” or “Submitted?” or “#INVALID OPERATION”

-- if [Submission Status] is “Submitted” or “Submitted, pending follow-up” AND [Redact Review] is “Yes” or “Yes (see notes)" AND [Redact Submission] is blank AND [Redact Status] is blank

-- [Copy of Submission] is “Pending upload”

I got all the "TBD" statements to work with =IF(OR(CONTAINS("TBD", Lead@row), CONTAINS("TBD", [Due Date]@row), CONTAINS("TBD", [Submission Date]@row), CONTAINS("TBD", [Other Information]@row), CONTAINS("TBD", [QA Team Notes]@row)), "Yes")

But when I try to add additional statements, it stops working. This was my last attempt:

=IF(OR(CONTAINS("TBD", Lead@row), CONTAINS("TBD", [Due Date]@row), CONTAINS("TBD", [Submission Date]@row), CONTAINS("TBD", [Other Information]@row), CONTAINS("TBD", [QA Team Notes]@row)), Escalation@row <> “”, “Yes”))

Thanks in advance for any insight you can provide!!

Dana G

Hi @Steven Stikons, After lots of head scratching I figured out that the quotes ("") weren't copy/pasting properly from Word (where I was building the formulas)! Once determined I was able to troubleshoot the rest of the formula:

=IF(OR(CONTAINS("TBD", Lead@row), CONTAINS("TBD", [Due Date]@row), CONTAINS("TBD", [Submission Date]@row), CONTAINS("TBD", [Other Information]@row), CONTAINS("TBD", [QA Team Notes]@row), [Escalation2]@row <> "", [Escalation Notes]@row <> "", OR([Submission Status]@row = "Submitted, pending Follow-up", [Submission Status]@row = "Pending Request", [Submission Status]@row = "#INVALID OPERATION"), AND(OR([Submission Status]@row = "Submitted", [Submission Status]@row = "Submitted, pending Follow-up"), OR([Redact Review]@row = "Yes", [Redact Review]@row = "Yes"), [Redact Submission Date]@row = "", [Redact Status]@row = ""), [Copy of Submission]@row = "Pending upload"), 1)

Thanks so much for your help!