Status Updates for Parent rows of Children
I would like the parent of fields in a Status row to reflect Complete when all children are marked Complete or Cancelled. If all children are marked "Not started", then the parent should be marked "Not started". Otherwise, if there is a combination in status for children other than above, the parent should be marked "In progress". I used the formula below but it seems not to follow what I want it to do. Can anyone guide me to my error(s)?
=IF(COUNTIF(CHILDREN(), "Not started") = COUNT(CHILDREN()), "Not started", IF(COUNTIF(CHILDREN(), "In Progress") = COUNT(CHILDREN()), "In progress", IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Cancelled") = COUNT(CHILDREN()), "Cancelled", IF(COUNTIF(CHILDREN(), "Complete") + COUNTIF(CHILDREN(), "Cancelled") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "In progress") > 0, "In progress", "Complete"))))))
Best Answer
-
Try something like this:
=IF(COUNTIFS(CHILDREN(), OR(@cell = "Complete", @cell = "Cancelled")) = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))
Answers
-
Try something like this:
=IF(COUNTIFS(CHILDREN(), OR(@cell = "Complete", @cell = "Cancelled")) = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))
-
Thank you - that worked!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!