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
-
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.
-
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")))
-
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"))))
-
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"))))))
-
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", ..........................
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!