How to combine two IF formulas into one formula

11/30/21
Accepted

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

  • Heather DuffHeather Duff ✭✭✭✭✭
    Accepted 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

  • Heather DuffHeather Duff ✭✭✭✭✭
    Accepted 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

  • Excellent! Thank you so much!

  • Heather DuffHeather Duff ✭✭✭✭✭

    @mballen Happy to help!

Sign In or Register to comment.