Help with multiple IF (CONTAINS) AND OR criteria

Josh Caldwell

Hello! I'm trying to figure out how to write a formula to return a value of "Open" or "Closed" when multiple criteria is met, or leave blank if N/A.

Columns:

• "Issue Category (Consolidated)" column contains selections "Invoice Mgmt", "Inventory", etc.
• "Status" dropdown column = Not Started, In Progress, Completed, Terminated

The below formula works when just looking for corresponding row containing "Invoice Mgmt" and returns the value "Open" (Note: replaced true/false values with "Open/Closed" dropdown)

=IF(CONTAINS("Invoice Mgmt", [Issue Category (Consolidated)]@row:[Issue Category (Consolidated)]@row), "Open", "")

… But, I also need to include IF "Status" column = "Completed" then set to "Closed", if it's still "In Progress" it can stay "Open"... to summarize, the logic needed is:

IF "Issue Category (Consolidated)" CONTAINS "Invoice Mgmt" AND "Status" <> "Completed" or "Terminated" THEN value returns "Open"

IF "Issue Category (Consolidated)" CONTAINS "Invoice Mgmt" AND "Status" = "Completed" or "Terminated" THEN value returns "Closed"

IF "Issue Category (Consolidated)" does NOT CONTAIN "Invoice Mgmt" then leave blank, regardless of "Status"

Bassam Khalil

Hi @Josh Caldwell 

Hope you are fine, please try the following formula:

=IF(AND(OR(Status@row = "Terminated", Status@row = "Completed"), HAS([Issue Category (Consolidated)]@row, "Invoice Mgmt")), "Closed", IF(NOT(HAS([Issue Category (Consolidated)]@row, "Invoice Mgmt")), "", "Open"))

the following screenshot shows the result:

Paul Newcome

Building off of what you already have from Victoria, this would be my suggestion:

=IF(CONTAINS("Invoice Mgmt", [Issue Category (Consolidated)]@row), IF(AND(Status@row <> "Terminated", Status@row <> "Completed"), "Open", "Closed"), "")

Josh Caldwell

Thank you! @Bassam Khalil - that worked! @Paul Newcome - this worked as well!

Victoria_Indimar

Hi Josh,

I think you could use another embedded if statement:

=IF(CONTAINS("Invoice Management", [Issue Category (Consolidate)]@row), IF(Status@row <> "Complete", "Open", "Closed"), "")

So it reads:

If Issue Category contain Invoice Management

If yes, then check if Status is NOT Complete

If yes, then set to Open

If no, then set to Closed

If no, then set to Blank

Hope I understood your question and that this helps!

Josh Caldwell

Hi @Victoria Welsh,

This definitely helped, had to tweak it a little to exact text, but mostly worked!

=IF(CONTAINS("Invoice Mgmt", [Issue Category (Consolidated)]@row), IF(Status@row <> "Completed", "Open", "Closed"), "")

Only other condition in here is <> "Completed" OR "Terminated" as either or Status would be considered "Closed". Do you know how I can include "Terminated" Status as well in this formula?

Thank you!

Paul Newcome

What if the Status is "Not Started"? Would you want blank?

Josh Caldwell

Hi @Paul Newcome,

I would want "Not Started" to still be "Open" because it's in the backlog of issues to fix and gets reported into a Dashboard.

"Not Started" "In Progress" "Pending Customer" "Tracking Only" can all be considered Open

"Completed" "Terminated" can be considered "Closed"

I only want 'Blank' if "Issue Category (Consolidated)" column does not Contain "Invoice Mgmt"

Bassam Khalil

Hi @Josh Caldwell 

Hope you are fine, please try the following formula:

=IF(AND(OR(Status@row = "Terminated", Status@row = "Completed"), HAS([Issue Category (Consolidated)]@row, "Invoice Mgmt")), "Closed", IF(NOT(HAS([Issue Category (Consolidated)]@row, "Invoice Mgmt")), "", "Open"))

the following screenshot shows the result:

Paul Newcome

Building off of what you already have from Victoria, this would be my suggestion:

=IF(CONTAINS("Invoice Mgmt", [Issue Category (Consolidated)]@row), IF(AND(Status@row <> "Terminated", Status@row <> "Completed"), "Open", "Closed"), "")

Josh Caldwell

Thank you! @Bassam Khalil - that worked! @Paul Newcome - this worked as well!

Bassam Khalil

@Josh Caldwell

You are welcome and I will be happy to help you anytime.

Paul Newcome

Happy to help. 👍️