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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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"))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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", ..........................
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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"))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!