Indicate when Departments are Different

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)

Best Answer

  • Melissa Yamada
    Melissa Yamada ✭✭✭✭✭
    Answer ✓

    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)

    Melissa Yamada
    melissa@insightfulsheets.com
    Data made simple, spreadsheets reimagined

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!