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!

Steven Stikons

Hi Dana,

You closed your OR function too soon and have one too many close parentheses at the end of the formula.

Try this:

=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")

Dana G

Thanks @Steven Stikons! Glad to know I'm on the right track. However, I tried the formula and it's showing as unparseable. I tried a few different columns instead of the Escalation column, in case the issue was with that particular column, but they all return this same error.

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!