If statements with multiple conditions

Ireland Adgate
edited 12/09/19 in Smartsheet Basics

I have a project status column that uses the circular colored symbols . There are indented rows with symbols and I want the parent row to have a green circle if all of the indented rows have green symbols. If there is at least one yellow circle, I want the parent circle to be yellow. If there is at least one red symbol, I want the parent symbol to be red. Any help would be appreciated.

Comments

  • sean59916
    sean59916 ✭✭✭

    Hi Ireland - 

     

    Can you clarify what you would expect to see at the parent row if you have 3 children - one is green, yellow, red. Are you expecting a yellow or red parent icon?

     

    Sean

  • Yes, sorry for the confusion. Red is the most severe and would make the parent red. The parent would be yellow if any of the children are yellow AND none of them are red.

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

    Hi Ireland,

    Try something like this.

    Place the formula below in the Parent of the Status column.

    =IF(COUNTIF(CHILDREN(); "Green") = COUNT(CHILDREN()); "Green"; IF(COUNTIF(CHILDREN(); "Red") > 0; "Red"; "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", "Yellow"))

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

    Did it work?

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    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.