How to combine two IF formulas into one formula
Hey Smartsheet Community,
I was able to get the info I wanted using three different formulas but I wanted to know if it was possible to make this work with one big formula?
Formula 1 - Symbol Colors for Major Project Completion Status
=IF(AND([*Dropdown-Single*]17 = "Major Project", [*Date*]6 < TODAY(-30), [*Text*]55 < 90), "Red", "Green")
Formula 2 - Symbol Colors for Non-Major Project Completion Status
=IF(AND([*Dropdown-Single*]17 <> "Major Project", [*Date*]6 < TODAY(-5), [*Text*]55 < 90), "Red", "Green")
Formula 3 - Takes the info from Formula 1 and Formula 2 and displays correct color based on the project type
=IF([*Dropdown-Single*]17 = "Major Project", [*Symbol*]75, [*Symbol*]76)
Is it possible for me to combine Formula 1 and 2 into one cell instead of using 3 cells to get to my desired goal?
Thank you!
-Myles
Best Answer
-
Hi @mballen ,
Yes! Here's what you'll do:
=IF(AND([*Dropdown-Single*]17 = "Major Project", [*Date*]6 < TODAY(-30), [*Text*]55 < 90), "Red", IF(AND([*Dropdown-Single*]17 <> "Major Project", [*Date*]6 < TODAY(-5), [*Text*]55 < 90), "Red", "Green"))
You could also use an OR statement, since both of the sets of criteria are being marked red:
=IF(OR(AND([*Dropdown-Single*]17 = "Major Project", [*Date*]6 < TODAY(-30), [*Text*]55 < 90), AND([*Dropdown-Single*]17 <> "Major Project", [*Date*]6 < TODAY(-5), [*Text*]55 < 90)), "Red", "Green")
Let me know if it works!
Best,
Heather
Answers
-
Hi @mballen ,
Yes! Here's what you'll do:
=IF(AND([*Dropdown-Single*]17 = "Major Project", [*Date*]6 < TODAY(-30), [*Text*]55 < 90), "Red", IF(AND([*Dropdown-Single*]17 <> "Major Project", [*Date*]6 < TODAY(-5), [*Text*]55 < 90), "Red", "Green"))
You could also use an OR statement, since both of the sets of criteria are being marked red:
=IF(OR(AND([*Dropdown-Single*]17 = "Major Project", [*Date*]6 < TODAY(-30), [*Text*]55 < 90), AND([*Dropdown-Single*]17 <> "Major Project", [*Date*]6 < TODAY(-5), [*Text*]55 < 90)), "Red", "Green")
Let me know if it works!
Best,
Heather
-
Excellent! Thank you so much!
-
@mballen Happy to help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 443 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!