Formula that compares three Columns on the same Row

Ami Veltrie

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

bbates

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

bbates

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

@bbates You're AMAZING! Works perfectly.

Many thanks!!