IF Formulas for Status Columns



I'm looking for assistance in writing an IF formula to change a cell's value based on the value of other cells in the same column. Specifically, the values will be symbols. If all symbols are green, I'd like the first row to show as green, if any are yellow, then the top row should be yellow, and lastly if any are red the top row should be red. 

Would the best way to achieve these results be to write an IF formula? 

Thanks in advanced!



  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭


    In my example, I've assumed that the top row is a parent.

    Try something like this.

    =IF(COUNTIF(CHILDREN(); "Green") = COUNT(CHILDREN()); "Green"; IF(COUNTIF(CHILDREN(); "Red") > 0; "Red"; IF(COUNTIF(CHILDREN(); "Yellow") > 0; "Yellow")))

    The same version but with the below changes for your and others convenience.

    =IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow")))

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it help/work?

    Have a fantastic day!


    Andrée Starå

    Workflow Consultant @ Get Done Consulting


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Making the same assumption as Andree, here is a second solution...


    =IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", "Green"))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!