Indicate when Departments are Different

fryeaf

Is there a formula to indicate when data in 2 columns are not empty and not equal? For instance, I want to know that a case is Interdisciplinary (using a checkbox or other mechanism in a separate column) when PI Department (selected from a drop down) is not in the same department as Co-PI Dept (selected from a drop down), Co-PI 2 Department (selected from a drop down), OR Co-PI 3 Department (selected from a drop down). What I have currently isn't working properly- it will indicate when the Department is the same.

Current formula (doesn't include Co-PI 3 Dept, but needs to) is: =IF(OR(NOT(ISBLANK([Co-PI Dept]@row)), NOT(ISBLANK([Co-PI 2 Dept]@row))), IF(OR([PI Department]@row <> [Co-PI Dept]@row, [PI Department]@row <> [Co-PI 2 Dept]@row), 1, 0), 0)

Melissa Yamada

Hello @fryeaf

Try this:

=IF(OR(AND([PI Department]@row <> [Co-PI Department]@row, [Co-PI Department]@row <> ""), AND([PI Department]@row <> [Co-PI 2 Department]@row, [Co-PI 2 Department]@row <> ""), AND([PI Department]@row <> [Co-PI 3 Department]@row, [Co-PI 3 Department]@row <> "")), 1, 0)