Multiple IF Statements

Micayla Ryan
edited 08/12/20 in Smartsheet Basics

Hello! Working on an formula where the Status will indicate the Health. Complete = Green, In Progress = Yellow, Not Started = Gray, and On Hold = Red.

This formula works: =IF((Status@row) = “Complete”, "Green”)

And according to other articles I read here, multiple IF statements can be linked together by commas (some have different brackets or number of brackets) but I've tried the variations below with no luck:

=IF((Status@row) = "Complete", "Green”), IF((Status@row) = “On Hold”, “Red”), IF((Status@row) = “Not Started”, “Gray”)))

=IF((Status@row) = “Complete”, "Green”), =IF((Status@row) = “In Progress“, “Yellow”), =IF((Status@row) = “Not Started“, “Gray”), =IF((Status@row) = “At Risk“, “Yellow”)

=IF([status]@row = “Complete”, “Green”, (IF([Status]@row = “In Progress], “Yellow”, IF([Status]@row = “On Hold”, “Red”), IF[Status]@row = “At Risk”, “Red”)))))

=IF((Status@row) = "Complete", "Green”), IF((Status@row) = “On Hold”, “Red”), IF((Status@row) = “Not Started”, “Gray”)

Can anyone let me know what I'm doing wrong?

Thank you!

Best Answer


  • Micayla Ryan

    Ah, thank you! I was also closing off each IF statement with a ) which I see happens at the end.

  • Chris Jernigan

    @David Joyeuse

    Trying to follow your logic and modify it for my sheet. My leadership wants the colors to drive the State

    IE: Green= In Progress, Yellow= Impeded, Red=Late, Blue=Complete, Blank=Not Started

    I took your formula and tried to modify it but get the Unparsable error.

    RYG is my Status column, and State is my Text field.

    What I have for my formula is:

    =IF([Status]@row="Green", "In Progress", IF([Status]@row="Yellow", "Impeded", IF([Status]@row="Red","Late", IF ([Status]@row="Blue","Complete"))))

    Any help would be appreciated, Thank you.

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    Hi @Chris Jernigan

    Your formula seems correctly written, though 2 quotes doesn't look like the others prior to Red and Late. Maybe that's a problem if you copy/pasted your formula from Excel or the formula from my previous post.

    Also, that's probably a typo, but your status column should be RYGB, not RYG.

    And you left out the Not Started status.

    So try copy this:

    =IF(Status@row="Green", "Complete", IF(Status@row="Yellow", "Impeded", IF(Status@row="Red", "Late", IF(Status@row="Blue", "Complete", "Not Started"))))

    If you still get the #UNPARSEABLE, try retype the whole formula, delete cells and click on the status cell, that happens sometimes. Updating the sheet may also solve the problem at times.

    Hope it helped!