Formula that compares three Columns on the same Row

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):
- All three columns are populated and all three are different values = Reviewing
- Two of the three columns are populated and both are different values = Reviewing
- All three columns are populated, two of the three columns are the same value = Partial (2 of 3)
- All three columns are populated and all three are the same value = Holistic
- Two of the three columns are populated and both are the same value = Holistic
- Only Utility CR is populated = Utility Only
- Only Telecom CR is populated = Telecom Only
- 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
-
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
-
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 You're AMAZING! Works perfectly.
Many thanks!!
Help Article Resources
Categories
Check out the Formula Handbook template!