Getting Status of Children to roll up to Parent Row

Virginia F

I have Parent tasks with Children tasks in a dropdown list column. The options for the children are: assigned, in progress, complete.

 would like to have the parent status display be blank if none of the children have a status selected (all are blank), display "Complete" only if all the children have a complete status, display "Not Started" if all of the children have an assigned status, and display "In Progress" if any of the children have an In Progress status (or as the default display). I've been playing with formulas for two days and can't get it to do that. I think the problem is it's not counting blank cells. But I need it to count blank cells for this to work the way I have it set up. 

 

Below is my current formula. Can someone please help me. 

=IF(COUNTIF(CHILDREN([Task Name]1), " ") = COUNT(CHILDREN([Task Name]1)), " ", IF(COUNTIF(CHILDREN([Task Name]1), "Complete") = COUNT(CHILDREN([Task Name]1)), "Complete", IF(COUNTIF(CHILDREN([Task Name]1), "In Progress") >= 1, "In Progress", IF(COUNTIF(CHILDREN([Task Name]1), "Assigned") = COUNT(CHILDREN([Task Name]1)), "Not Started"))))

Paul Newcome

Remove the spaces between your quotes to account for blank cells.

 

Instead of " ", try "".