Formula for a parent row to populate status based on children status

alexis.ray89371

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!

Frank B.

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:

1. 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".
2. 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".
3. 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".
4. 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!

Frank B.

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:

1. 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".
2. 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".
3. 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".
4. 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!

alexis.ray89371

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?

alexis.ray89371

Nevermind. I figured it out. Thank you so much!!