# Multiple IF/AND statements

Options
✭✭✭✭✭
edited 12/09/19

I am a new user and struggling with some IF statement rules to set color status.  For the most part, I am pulling from the status column called “Capability - Stage Gate Status”.  Users choose from a drop list.  In addition, I wanted to add some criteria for other scenarios, such as the Start Date is past and % is 0%; the Start date is the future, or the task is N/A.  I am using the RYGB color balls to capture the color or the short text for when something is FUT or N/A.  It seems to work fine until I add the rule for “N/A”.  Then, it just turns the color ball to Red and my newly added formula is not saved.

Here are my rules:

Blue:  If “% Complete” is 100%, then set the color to blue

Green:  If the “Capability - Stage Gate Status” is set to "On Track" then set the status ball column to "Green”

Yellow:   If the “Capability - Stage Gate Status” is set to "Delayed/Late" then set the status ball column to "Yellow”

Red:  If the “Capability - Stage Gate Status” is set to "At Risk" then set the status ball column to "Red”

Red:  IF Start Date is less than today AND % Complete <= 0% then set the status ball column to “Red”

FUT (Future task):  If the start date is in the future (greater than today), then set the status ball column to “FUT”

N/A (Not Applicable):  If the ““Capability - Stage Gate Status” is set to ”Not Applicable”, then set the status ball to “N/A”

Here is my formula breakdown:

=IF([% Complete]@row = 1, "Blue")

IF([Capability - Stage Gate Status]@row = "On Track", "Green")

IF([Capability - Stage Gate Status]@row = "Delayed/Late", "Yellow")

IF([Capability - Stage Gate Status]@row = "At Risk", "Red")

IF(AND([Start Date]@row < TODAY(), [% Complete]@row <= 0), "Red")

IF([Start Date]@row > TODAY(), "FUT")

IF([Capability - Stage Gate Status]@row = "Not Applicable", "N/A")

Here is the formula strung together…you can see it’s missing the last rule for “N/A”.  It just disappears and the ball turns to Red.

=IF([% Complete]@row = 1, "Blue", IF([Capability - Stage Gate Status]@row = "On Track", "Green", IF([Capability - Stage Gate Status]@row = "Delayed/Late", "Yellow", IF([Capability - Stage Gate Status]@row = "At Risk", "Red", IF(AND([Start Date]@row < TODAY(), [% Complete]@row <= 0), "Red", IF([Start Date]@row > TODAY(), "FUT"))))))

I am amazed at how far I seemed to get.  Any help is greatly appreciated.

Thank you!

Ilene

• ✭✭✭✭✭✭
Options

Because of how nested statements work, it is simply an order of operations issue. Once the formula comes across a true value, it will not continue with the rest. A blank date cell is always considered less than TODAY() and a blank percentage will always be considered zero. That means it is getting to that specific "Red" criteria, finding it true, and then stopping. Try moving the N/A portion to the beginning of your nested IF statement.

• ✭✭✭✭✭
Options

That did the trick!  Thank you so much!!

• ✭✭✭✭✭✭
Options

Excellent! Happy to help!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!