#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Formula with multiple conditions and logic

Options
edited 12/09/19

Help! I'm trying to create a formula that automates RYG balls based on many different factors across multiple cells. I'm having trouble because the "IF" formula that I created is stopping at the first point of validation that it finds to be true and instead, I need it to keep moving on the validate all points.

Here's what I'm trying to make happen in one cell:

If (Begin) has a checkbox, yellow, if (Begin) doesn't have a checkbox, blank. AND THEN if (Due) is blank, gray. AND THEN if (Actual) contains date and (Due) is blank, green. AND THEN if (Actual) is less than or equal to (Due), green. AND THEN if (Actual) is greater than (Due), red.

Tags:

• Options

Here's an example of one of mine that might help.

=IF(ISDATE([Date Sent to Customer]5), "Blue", IF(ISDATE([Date Released to SAM]5), "Green", IF(NETWORKDAYS(TODAY(), [Date Needed]5) <= 2, "Red", IF(NETWORKDAYS(TODAY(), [Date Needed]5) <= 4, "Yellow"))))

• Options

This will need to be a nested IF statement with AND conditions.

If you are having trouble, show us the formula you created and we can troubleshoot with you!

I would attempt to build it for you, but I dont have the time at the moment

• ✭✭✭✭✭✭
edited 10/02/16
Options

@Ian

Thanks so much for posting your formula.  I had a formula I thought would work with just two conditions, but in order for it to work right I needed four conditions defined.  Your example saved my day.  With mine, i wanted to track safety bypasses, so I needed the status to stay red unless the bypass was removed, and ONLY if the bypass was removed on a day >= to the day it was installed.

=IF(

AND(ISBLANK(Description15), ISBLANK(Signal15), ISBLANK(Location15), ISBLANK(Reason15), ISBLANK([Installed Date]15))

, "",

IF(ISBLANK([Removed Date]15)

, "Red",

IF(([Installed Date]15 <= [Removed Date]15)

, "Green",

IF(([Installed Date]15 > [Removed Date]15)

, "Red"

))))

This discussion has been closed.