Status updating parent row with conditions

I am trying to update the parent row for the following:

If any children = In Progress, Not Started, or blank then In Progress

If all children = Pass then Pass

If any children = fail then Fail

If any children = retest then Retest

If any children = Out of scope or Deferred - Future, then defer to the other rules to determine status (1 is out of scope and all others are pass then pass, 1 is out of scope, 1 is retest and all others are pass then retest)

I have written the below to account for all but the out of scope and deferred. I have tested it and know it works for all statues including returning data for blank fields.

=IF(COUNTIF(CHILDREN(), "Fail") > 0, "Fail", IF(COUNTIF(CHILDREN(), "Retest") > 0, "Retest", IF(COUNTIFS(CHILDREN(), OR(@cell = "In Progress", @cell = "Not Started", @cell = "")) > 0, "In Progress", IF(COUNTIF(CHILDREN(), @cell = "Pass") = COUNT(CHILDREN()), "Pass"))))

Can you help me add the formula for If any children = Out of scope or Deferred - Future, then defer to the other rules to determine status (1 is out of scope and all others are pass then pass, 1 is out of scope, 1 is retest and all others are pass then retest). I have been running in circles.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    All you should need to do is adjust the "Pass" argument to be an OR(@cell = "Pass", @cell = "Out of scope or Deferred - Future").


    The rest should work fine already because you are saying to output (for example) "Retest" if there is at least one regardless of the other statuses.

  • allison.worley
    allison.worley ✭✭✭✭
    edited 12/07/23

    This would work, Thank you. However today I was asked to change where the not started and blank do not follow in Progress unless there is a pass or in progress noted:

    I like to take each part apart to understand the formula. Below are my thoughts.

    If any children = fail then Fail

    If any children = in progress then In Progress

    If all children = Pass then Pass

    *If all children = Not Started, Blank or a combination of blank and not started = Not started

    *If even one in progress or Pass and the rest not started or blank then = In Progress

    I cannot find a combination of the last 2 where one does not override the other = either not started or In progress. I have been working on this half the week so really appreciate your help above and any future feedback.

    If any children = fail then Fail IF(COUNTIF(CHILDREN(), "Fail") > 0, "Fail"

    If any children = retest then Retest IF(COUNTIF(CHILDREN(), "Retest") > 0, "Retest", 

    If all children = Pass then Pass IF(COUNTIF(CHILDREN(), @cell = "Pass") = COUNT(CHILDREN()), "Pass"

    ***My challenge is the All not started or blank and the Not Started, blank, Pass = In progress combinations. I have tried the below plus about 100 more combinations. I have tried breaking it down to build it back together but this puzzle is just not fitting

    if all blank or not started then Not Started IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Started", @cell = "")) > 0, "Not Started" Always returns Not Started

    If any children = in progress then In Progress: IF(COUNT(CHILDREN(), "In Progress") "In Progress" Always returns "In Progress"

    • IF(COUNTIFS(CHILDREN(), OR(@cell = "In Progress", @cell = "Not Started", @cell = "")) > 0, "In Progress" this always returns in progress even if all are not started or Blank

    ***Below works for all scenarios except if only Pass and Blank.

    =IF(COUNTIF(CHILDREN(), "Fail") > 0, "Fail", IF(COUNTIF(CHILDREN(), @cell = "Pass") = COUNT(CHILDREN()), "Pass", IF(COUNTIF(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress")))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try this:


    =IF(COUNTIFS(CHILDREN(), @cell = "Fail")> 0, "Fail", IF(COUNTIFS(CHILDREN(), @cell = "Retest")> 0, "Retest", IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Started", @cell = "")) = COUNT(CHILDREN()), "Not Started", IF(AND(COUNTIFS(CHILDREN(), @cell = "") = 0, COUNTIFS(CHILDREN(), @cell = "Pass") = COUNT(CHILDREN())), "Pass", "In Progress"))))

  • allison.worley
    allison.worley ✭✭✭✭
    edited 12/07/23

    Adding in the "Out of scope" and "Deferred - Future" options I have made the below work except for the below. any help?

    if all are Out of Scope and Deferred combination (This will = pass which I think may be ok)

    The other problem as noted earlier is if pass and Blank combination, out of scope and blank combination, or Deferred- Future and blank combination (this should = In progress) but is showing up as the value associated.

    =IF(COUNTIF(CHILDREN(), "Fail") > 0, "Fail", IF(COUNTIF(CHILDREN(), "Retest") > 0, "Retest", IF(COUNTIF(CHILDREN(), @cell = "Deferred - Future") = COUNT(CHILDREN()), "Deferred - Future", IF(COUNTIF(CHILDREN(), @cell = "Out of Scope") = COUNT(CHILDREN()), "Out of Scope", IF(COUNTIF(CHILDREN(), OR(@cell = "Pass", @cell = "Out of scope", @cell = "Deferred - Future")) = COUNT(CHILDREN()), "Pass", IF(COUNTIF(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You missed the AND portion for the "Pass" argument to include that there are no blanks.


    =.............................., IF(AND(COUNTIFS(CHILDREN(), @cell = "") = 0, COUNTIFS(CHILDREN(), @cell = "Pass") = COUNT(CHILDREN())), "Pass", ..........................

  • allison.worley
    allison.worley ✭✭✭✭

    Paul, thank you for responding. I am not sure I am putting this in the formula correctly because it is still returning Pass. If there is pass and blank combination it should return "In progress". I have changed this so many directions. Currently my formula solves for all except:

    Blank and Out of scope combination = In Progress

    Blank and Pass combination = In Progress

    Blank and Deferred - future combination = In Progress

    =IF(COUNTIF(CHILDREN(), "Fail") > 0, "Fail", IF(COUNTIF(CHILDREN(), "Retest") > 0, "Retest", IF(COUNTIF(CHILDREN(), @cell = "Deferred - Future") = COUNT(CHILDREN()), "Deferred - Future", IF(COUNTIF(CHILDREN(), @cell = "Out of Scope") = COUNT(CHILDREN()), "Out of Scope", IF(COUNTIF(CHILDREN(), OR(@cell = "Pass", @cell = "Out of scope", @cell = "Deferred - Future")) = COUNT(CHILDREN()), "Pass", IF(COUNTIF(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))))))


    Where do I add the AND statement...

    IF(AND(COUNTIFS(CHILDREN(), @cell = "") = 0, COUNTIFS(CHILDREN(), @cell = "Pass") = COUNT(CHILDREN())), "Pass",

    ...and how will that make the end result In Progress? I aspire to be as good at formulas, so I want to understand the logic as well.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would drop it into your formula in the same place I have it in my example. In the logical statement portion that outputs "Pass".

    =IF(COUNTIFS(CHILDREN(), @cell = "Fail")> 0, "Fail", IF(COUNTIFS(CHILDREN(), @cell = "Retest")> 0, "Retest", IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Started", @cell = "")) = COUNT(CHILDREN()), "Not Started", IF(AND(COUNTIFS(CHILDREN(), @cell = "") = 0, COUNTIFS(CHILDREN(), @cell = "Pass") = COUNT(CHILDREN())), "Pass", "In Progress"))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!