Help with COUNTIFS and CHILDREN Function

cferias

Hello! I am trying to get a formula to, automatically update status colors for the child and parent.

1. The Parent is distinguished by "Main Project" in the "Project or Task" Column.
2. I would like the Parent Row to update to show the worst status that aligns to the children status (i.e. if all children are green, parent is green, if one child is red/yellow the parent is red/yellow)
3. If the child row also has a "Complete" status i would like the parent row to show green.
4. The Child status are determined by the end dates. Green is anything due greater than 10 days from today, yellow is from today - 10 days and red is anything before today.
5. If the end date it TBD then the status row can be TBD

I cannot for the life of me can get the parent rows to align to the children status, and especially cannot the "OR" statement for the "completed" to turn the parent row green. HELP!

=IF([Project or Task]@row = "Main Project", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", "Gray"))), IF([Task Status]@row = "Complete", "Complete", (IF([Task Status]@row = "Closed", "Closed", IF(OR([Task Status]@row = "On Hold", [Task Status]@row = "Cancelled", [Task Status]@row = "Rejected"), "Gray", IF([End Date]@row = "TBD", "TBD", IF(AND(OR([Task Status]@row = "Assigned", [Task Status]@row = "On Track"), [End Date]@row <= TODAY()), "Red", IF(AND(OR([Task Status]@row = "Assigned", [Task Status]@row = "On Track"), [End Date]@row <= TODAY(10)), "Yellow", IF(AND(OR([Task Status]@row = "Assigned", [Task Status]@row = "On Track", [Task Status]@row = "Not Started"), [End Date]@row - TODAY() > 10), "Green")))))))))

Eric Law

@cferias I added in an OR for the Green and Complete for children and it seems to work now for me.

=IF([Project or Task]@row = "Main Project", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), OR(@cell = "Green", @cell = "Complete")) = COUNT(CHILDREN()), "Green", "Gray"))), IF([Task Status]@row = "Complete", "Complete", (IF([Task Status]@row = "Closed", "Closed", IF(OR([Task Status]@row = "On Hold", [Task Status]@row = "Cancelled", [Task Status]@row = "Rejected"), "Gray", IF([End Date]@row = "TBD", "TBD", IF(AND(OR([Task Status]@row = "Assigned", [Task Status]@row = "On Track"), [End Date]@row <= TODAY()), "Red", IF(AND(OR([Task Status]@row = "Assigned", [Task Status]@row = "On Track"), [End Date]@row <= TODAY(10)), "Yellow", IF(AND(OR([Task Status]@row = "Assigned", [Task Status]@row = "On Track", [Task Status]@row = "Not Started"), [End Date]@row - TODAY() > 10), "Green")))))))))

Eric Law

@cferias I added in an OR for the Green and Complete for children and it seems to work now for me.

=IF([Project or Task]@row = "Main Project", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), OR(@cell = "Green", @cell = "Complete")) = COUNT(CHILDREN()), "Green", "Gray"))), IF([Task Status]@row = "Complete", "Complete", (IF([Task Status]@row = "Closed", "Closed", IF(OR([Task Status]@row = "On Hold", [Task Status]@row = "Cancelled", [Task Status]@row = "Rejected"), "Gray", IF([End Date]@row = "TBD", "TBD", IF(AND(OR([Task Status]@row = "Assigned", [Task Status]@row = "On Track"), [End Date]@row <= TODAY()), "Red", IF(AND(OR([Task Status]@row = "Assigned", [Task Status]@row = "On Track"), [End Date]@row <= TODAY(10)), "Yellow", IF(AND(OR([Task Status]@row = "Assigned", [Task Status]@row = "On Track", [Task Status]@row = "Not Started"), [End Date]@row - TODAY() > 10), "Green")))))))))

cferias

THANK YOU. I have been trying to make that or statement work!