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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!