Nested If Formula (Multiple Conditions)

Hello, Hoping to get some help for a rather complex nested if formula I'd like to build based on responses to a webform. Below are the conditions. Is this possible to create an if formula around these conditions to result in a a red/yellow/green visual symbol? Any help or guidance is much appreciated! Thanks so much!


  • L_123
    L_123 ✭✭✭✭✭✭

    the secret to this one is that yellow has all the power over the status. Only 2 responses outside of symptoms, one in positives, and one in call requested can impact the score. Taking this into account, you can use a much shorter if statement. I recommend locking the columns as dropdowns to prevent errors.

    =IF(JOIN([Household Positives]:[Call Requested], "") = "NoNoNo", "Green", IF(OR(CONTAINS("No", [Current Symptoms]@row), CONTAINS("Loss", [Current Symptoms]@row), CONTAINS("Sore", [Current Symptoms]@row)), "Yellow", "Red"))

    Good luck with fighting the corona :)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @L@123 I like your use of the JOIN for "Green", but here is how I approached it...

    "Red" is the only one where [Call Requested] is blank.

    "Yellow" is the only one where [Call Requested] is "Yes" or [Current Symptoms] is not "No". For the single entry where [Current Symptoms] is "No", [Call Requested] can only be "Yes".

    "Green" is all that's left.

    =IF([Call Requested]@row = "", "Red", IF(OR([Call Requested]@row = "Yes", [Current Symptoms]@row <> "No"), "Yellow", "Green"))

    Just my own person take on it.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!