IF, AND/OR, NOT formula with multiple conditions for a symbol column

Hello,

I have been using a symbol column to monitor updates for a master sheet of all of my departments projects: =IF([Exec Summary Update]@row < TODAY(-14), "Red", IF([Exec Summary Update]@row < TODAY(-8), "Yellow", "Green"))

However, I need to update this formula to include criteria that states NOT if the Phase@row is blank or "Closed"

I obviously can't figure out the proper syntax to insert into my formula as I keep getting #Incorrect Argument Set error messages.

If anyone could help me update my formula, that would be awesome!

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    I've split the answer into 3 steps to make it clear to anyone following along (but I don't think you need anything except the last part).

    1.

    This formula will put nothing into your symbol column if Phase is "Closed"

    =IF(Phase@row = "Closed", "", IF([Exec Summary Update]@row < TODAY(-14), "Red", IF([Exec Summary Update]@row < TODAY(-8), "Yellow", "Green")))

    Here it is in context

    2.

    This formula will put nothing into your symbol column if Phase is blank

    =IF(ISBLANK(Phase@row), "", IF([Exec Summary Update]@row < TODAY(-14), "Red", IF([Exec Summary Update]@row < TODAY(-8), "Yellow", "Green")))

    3.

    To combine both of these logics use

    =IF(OR(ISBLANK(Phase@row), Phase@row = "Closed"), "", IF([Exec Summary Update]@row < TODAY(-14), "Red", IF([Exec Summary Update]@row < TODAY(-8), "Yellow", "Green")))


Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    I've split the answer into 3 steps to make it clear to anyone following along (but I don't think you need anything except the last part).

    1.

    This formula will put nothing into your symbol column if Phase is "Closed"

    =IF(Phase@row = "Closed", "", IF([Exec Summary Update]@row < TODAY(-14), "Red", IF([Exec Summary Update]@row < TODAY(-8), "Yellow", "Green")))

    Here it is in context

    2.

    This formula will put nothing into your symbol column if Phase is blank

    =IF(ISBLANK(Phase@row), "", IF([Exec Summary Update]@row < TODAY(-14), "Red", IF([Exec Summary Update]@row < TODAY(-8), "Yellow", "Green")))

    3.

    To combine both of these logics use

    =IF(OR(ISBLANK(Phase@row), Phase@row = "Closed"), "", IF([Exec Summary Update]@row < TODAY(-14), "Red", IF([Exec Summary Update]@row < TODAY(-8), "Yellow", "Green")))


  • kelceyg
    kelceyg ✭✭✭✭

    @KPH - thank you for your thorough and accessible walk through! I loved it!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!