Formula that compares three Columns on the same Row

Options

Hello. I have a working formula that compares two columns:

=IF(OR([Utility CR]@row = "", [Telecom CR]@row = ""), "", IF([Utility CR]@row = [Telecom CR]@row, "Holistic", "Reviewing"))

Is it possible to add a third variable "Waste CR" and add some additional logic, as described below?

Scenarios (only one will apply to each Row):

  1. All three columns are populated and all three are different values = Reviewing
  2. Two of the three columns are populated and both are different values = Reviewing
  3. All three columns are populated, two of the three columns are the same value = Partial (2 of 3)
  4. All three columns are populated and all three are the same value = Holistic
  5. Two of the three columns are populated and both are the same value = Holistic
  6. Only Utility CR is populated = Utility Only
  7. Only Telecom CR is populated = Telecom Only
  8. Only Waste CR is populated = Waste Only

I am trying to automate the CR Support Type value, to result in one of these drop down options:

  • Reviewing
  • Partial (2 of 3)
  • Holistic
  • Utility Only
  • Telecom Only
  • Waste Only


Best Answer

  • bbates
    bbates ✭✭
    Answer ✓
    Options

    Here you go:

    =IF(AND([Utility CR]@row <> "", [Telecom CR]@row = "", [Waste CR]@row = ""), "Utility Only", IF(AND([Utility CR]@row = "", [Telecom CR]@row <> "", [Waste CR]@row = ""), "Telecom Only", IF(AND([Utility CR]@row = "", [Telecom CR]@row = "", [Waste CR]@row <> ""), "Waste Only", IF(OR(AND([Utility CR]@row = [Telecom CR]@row, [Waste CR]@row = ""), AND([Utility CR]@row = "", [Telecom CR]@row = [Waste CR]@row), AND([Utility CR]@row = [Waste CR]@row, [Telecom CR]@row = ""), (AND([Utility CR]@row = [Telecom CR]@row, [Utility CR]@row = [Waste CR]@row, [Telecom CR]@row = [Waste CR]@row))), "Holistic", IF(OR(AND([Utility CR]@row = [Telecom CR]@row, [Utility CR]@row <> [Waste CR]@row), (AND([Utility CR]@row <> [Telecom CR]@row, [Utility CR]@row <> [Waste CR]@row, [Telecom CR]@row = [Waste CR]@row)), (AND([Utility CR]@row <> [Telecom CR]@row, [Telecom CR]@row <> [Waste CR]@row, [Utility CR]@row = [Waste CR]@row))), "Partial (2 of 3)", IF(OR(AND([Utility CR]@row <> [Telecom CR]@row, [Waste CR]@row = ""), AND([Utility CR]@row <> [Waste CR]@row, [Telecom CR]@row = ""), AND([Utility CR]@row = "", [Telecom CR]@row <> [Waste CR]@row), AND([Utility CR]@row <> [Telecom CR]@row, [Utility CR]@row <> [Waste CR]@row, [Telecom CR]@row <> [Waste CR]@row)), "Reviewing", ""))))))


Answers

  • bbates
    bbates ✭✭
    Answer ✓
    Options

    Here you go:

    =IF(AND([Utility CR]@row <> "", [Telecom CR]@row = "", [Waste CR]@row = ""), "Utility Only", IF(AND([Utility CR]@row = "", [Telecom CR]@row <> "", [Waste CR]@row = ""), "Telecom Only", IF(AND([Utility CR]@row = "", [Telecom CR]@row = "", [Waste CR]@row <> ""), "Waste Only", IF(OR(AND([Utility CR]@row = [Telecom CR]@row, [Waste CR]@row = ""), AND([Utility CR]@row = "", [Telecom CR]@row = [Waste CR]@row), AND([Utility CR]@row = [Waste CR]@row, [Telecom CR]@row = ""), (AND([Utility CR]@row = [Telecom CR]@row, [Utility CR]@row = [Waste CR]@row, [Telecom CR]@row = [Waste CR]@row))), "Holistic", IF(OR(AND([Utility CR]@row = [Telecom CR]@row, [Utility CR]@row <> [Waste CR]@row), (AND([Utility CR]@row <> [Telecom CR]@row, [Utility CR]@row <> [Waste CR]@row, [Telecom CR]@row = [Waste CR]@row)), (AND([Utility CR]@row <> [Telecom CR]@row, [Telecom CR]@row <> [Waste CR]@row, [Utility CR]@row = [Waste CR]@row))), "Partial (2 of 3)", IF(OR(AND([Utility CR]@row <> [Telecom CR]@row, [Waste CR]@row = ""), AND([Utility CR]@row <> [Waste CR]@row, [Telecom CR]@row = ""), AND([Utility CR]@row = "", [Telecom CR]@row <> [Waste CR]@row), AND([Utility CR]@row <> [Telecom CR]@row, [Utility CR]@row <> [Waste CR]@row, [Telecom CR]@row <> [Waste CR]@row)), "Reviewing", ""))))))


  • Ami Veltrie
    Ami Veltrie ✭✭✭✭✭✭
    Options

    @bbates You're AMAZING! Works perfectly.

    Many thanks!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!