IF/AND formula that looks for multiple entries

Options

Hi,

I am trying to create a IF/AND formula to return a status color based on multiple inputs.

For example:

IF row title "Status" equals "complete" or "cancelled" or "rejected", then column titled "RISK" (the column with the formula) should be blank. IF row titled "Status" equals "in progress", then column "RISK" should be "green".

Here is an the example formula:

=IF(AND(Status@row <> "Complete", [Launch Date]@row < TODAY()), "Red", IF(AND(Status@row = "Complete", [Kickoff Date]@row < TODAY()), "", IF(AND(Status@row = "Cancelled", [Kickoff Date]@row <> TODAY()), "green", IF(AND(Status@row = "Rejected", [Kickoff Date]@row < TODAY()), "", IF(AND(Status@row <> "In Progress", [Kickoff Date]@row < TODAY(+5)), "Yellow", IF(AND(Status@row <> "Complete", [Launch Date]@row < TODAY(+5)), "Yellow", IF(AND(Status@row <> "In Progress", [Kickoff Date]@row < TODAY(+5)), "Yellow", "Green")))))))

This works as expected except if status = "Cancelled" or "Rejected".

Thanks in advance for any insight.

Tags:

Answers

  • Mike Stickney
    Options

    Reviewing my question...

    My assumption is the formula isn't working because the condition of <> "complete" already is met, so what I'm looking to do is to make it some kind of an "or" statement (so if it equals "completer" or "in progress", etc).

    Hope that clarifies my question.

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    Hi Mike, your first If Statement says that if anything other than "Complete" and Launch Date is before today, then mark it red.

    So when the Launch Date is before today and the status is "Cancelled", it's going to mark it red. Is that correct?

    If not, what were you expecting it to look at next?

    Ryan

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Mike Stickney
    Options

    Thanks for your response Ryan...

    I tried to clarify in my comment, that I expected that was the reason it was happening.

    Maybe a better question is instead of the first statement saying not equal (<>) how can I make it say "equal to "Complete" OR "In Progress" (or anything something).

    Let me know if that is a little more clear of what my problem is.

    Thanks

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    Options

    One mistake I noticed was in this line:

    [Kickoff Date]@row <> TODAY()), "green"

    Green needs to be capitalized there.

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    edited 10/12/22
    Options

    @Mike Stickney

    Also Mike if you reorder your if statements that can fix your issue.

    The formula always starts with the first if. If the condition is met, then it does that and ignores the rest.

    The order matters in this case.

    Take this for an example:

    x = 5

    if x is less than 25, "Green", if x is less than 10, "Yellow", If x is less than 6, "Red".

    In that example, the formula will produce "Green" even though x meets all those conditions.

    That's because the first condition was true, so the rest is ignored.

    This would need to be reworded to this:

    if x is less than 6, "Red', if x is less than 10, "Yellow", if x is less than 25, "Green".

    This would produce "Red" as the answer.

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    Options

    Long story short, move your cancelled and rejected to the beginning of your formula.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!