How to combine two IF formulas into one formula

mballen

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

Heather Duff

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

Heather Duff

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

mballen

Excellent! Thank you so much!

Heather Duff

@mballen Happy to help!