Is there a formula to specify "Completed" if children "Completed" or "Nothing to Submit"?

Sara Ross

The formula I'm using currently returns the cell as blank if children are "completed" or "nothing to submit", but I would like it to return "Completed" in that case.

=IF(COUNTIF(CHILDREN(), "Complete") + COUNTIF(CHILDREN(), "Nothing to Submit") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Not Started") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Blocked") > 0, "In Progress")))))

Paul Newcome

Oh! I see now.

Go back to CHILDREN but update "Complete" to "Completed".

Paul Newcome

Try using DESCENDANTS instead of CHILDREN.

Sara Ross

Unfortunately, even with this change the status cells for the parents ("Provide Submissions" and "Proposed Rec Dept Submissions") remain blank.

This is the updated formula I used...

=IF(COUNTIF(DESCENDANTS(), "Complete") + COUNTIF(DESCENDANTS(), "Nothing to Submit") = COUNT(DESCENDANTS()), "Complete", IF(COUNTIF(DESCENDANTS(), "Not Started") = COUNT(DESCENDANTS()), "Not Started", IF(COUNTIF(DESCENDANTS(), "In Progress") > 0, "In Progress", IF(COUNTIF(DESCENDANTS(), "Not Started") > 0, "In Progress", IF(COUNTIF(DESCENDANTS(), "Blocked") > 0, "In Progress")))))

Any other suggestions?

Thanks,

Sara

Paul Newcome

Oh! I see now.

Go back to CHILDREN but update "Complete" to "Completed".

Sara Ross

But of course! Ugh...the little details.

Thanks for being a second set of eyes!

Sara

Paul Newcome

Happy to help. 👍️