Formula

I have a series deliverables reference from another sheet (sheet 1). In this sheet (sheet 2) I list those deliverables under an objective and I want to rate the entire objective based on the highest risk in my Symbols (Red being the highest risk, then Yellow, Green, Gray) what would be the formula.

Same thing for the objective checkbox, if one check box is selected the objective is a checkbox.


Tags:

Best Answers

  • AravindGP
    AravindGP ✭✭✭✭✭
    Answer ✓

    Hi @Eddie Endurium LLC


    If I understand you correctly, you want Objective 1 (the parent row) to show Red if even one of its deliverables (children) is Red. If none are Red, then look for Yellow and make it Yellow if you find one, and so on.


    You can add this formula to your parent row for the Symbol column in Sheet 2


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


    For Objective column (which is a checkbox), you can use this formula in Sheet 2


    =IF(COUNTIF(CHILDREN(), 1)>0, 1, 0)

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

  • Eddie Endurium LLC
    Eddie Endurium LLC ✭✭✭✭
    Answer ✓

    Thank you Aravind! Much appreciated

Answers

  • AravindGP
    AravindGP ✭✭✭✭✭
    Answer ✓

    Hi @Eddie Endurium LLC


    If I understand you correctly, you want Objective 1 (the parent row) to show Red if even one of its deliverables (children) is Red. If none are Red, then look for Yellow and make it Yellow if you find one, and so on.


    You can add this formula to your parent row for the Symbol column in Sheet 2


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


    For Objective column (which is a checkbox), you can use this formula in Sheet 2


    =IF(COUNTIF(CHILDREN(), 1)>0, 1, 0)

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

  • Eddie Endurium LLC
    Eddie Endurium LLC ✭✭✭✭
    Answer ✓

    Thank you Aravind! Much appreciated

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!