Formula for a parent row to populate status based on children status
Hi! I looked through the community for similar posts, but can't get quite there with previous posts. My instance is slightly different than the ones I could find and I have been unable to modify it accurately.
I am trying to populate the status on the Parent row (grey bar in image below) based on the statuses in the children.
If ALL children = Not Started then Not Started
If ALL children = Completed then Completed
If at least one child = In Progress then In Progress
There may be instances where the status is blank because someone adds a new task and doesn't put the status in.
Thank you for any help!
Best Answer

HI @alexis.ray89371 try this:
=IF(
COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()),
"Not Started",
IF(
COUNTIF(CHILDREN(), "Completed") = COUNT(CHILDREN()),
"Completed",
IF(
COUNTIF(CHILDREN(), "In Progress") > 0,
"In Progress",
""
)
)
)
Here's how the formula works:
 The
COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN())
condition checks if the count of children with the status "Not Started" is equal to the total count of children. If true, it sets the parent status as "Not Started".  If the first condition is not met, the formula moves to the next condition:
COUNTIF(CHILDREN(), "Completed") = COUNT(CHILDREN())
. It checks if the count of children with the status "Completed" is equal to the total count of children. If true, it sets the parent status as "Completed".  If neither of the above conditions is met, the formula checks if there is at least one child with the status "In Progress" using the condition
COUNTIF(CHILDREN(), "In Progress") > 0
. If true, it sets the parent status as "In Progress".  If none of the conditions are met, it sets the parent status as blank.
This formula considers cases where the status is blank by setting an empty string as the default value when none of the conditions are met. Adjust the status values and conditions as needed based on your specific requirements.
HTH!
 The
Answers

HI @alexis.ray89371 try this:
=IF(
COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()),
"Not Started",
IF(
COUNTIF(CHILDREN(), "Completed") = COUNT(CHILDREN()),
"Completed",
IF(
COUNTIF(CHILDREN(), "In Progress") > 0,
"In Progress",
""
)
)
)
Here's how the formula works:
 The
COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN())
condition checks if the count of children with the status "Not Started" is equal to the total count of children. If true, it sets the parent status as "Not Started".  If the first condition is not met, the formula moves to the next condition:
COUNTIF(CHILDREN(), "Completed") = COUNT(CHILDREN())
. It checks if the count of children with the status "Completed" is equal to the total count of children. If true, it sets the parent status as "Completed".  If neither of the above conditions is met, the formula checks if there is at least one child with the status "In Progress" using the condition
COUNTIF(CHILDREN(), "In Progress") > 0
. If true, it sets the parent status as "In Progress".  If none of the conditions are met, it sets the parent status as blank.
This formula considers cases where the status is blank by setting an empty string as the default value when none of the conditions are met. Adjust the status values and conditions as needed based on your specific requirements.
HTH!
 The

Thank you so much! Great explanation.
Although, once I entered the formula I realized my logic doesn't quite fit all instances. You can see in the first parent row it is blank. I realized it is because it doesn't quite fit the logic I initially thought of. Ideally it would say "In Progress" because not all tasks are completed. There is one task "Not Started". Do you have a better suggestion to lay out the formula/logic to where this instance would be captured as "In Progress" as well?

Nevermind. I figured it out. Thank you so much!!
Help Article Resources
Categories
Check out the Formula Handbook template!