Formula calculating Status on Parent and Child Rows

Laurenzella

Right now, the overall project status formula displays as "Complete" only if all the checklist items are marked as "Completed". We have been asked to update the formula so that it if all checklist items are marked "Completed" OR "Cancelled" the overall project status will be "Complete". The "Tasks Not Needed" references a hidden column that tells certain tasks not to be included because they are checked off - this is accomplished with the Default filter.

Current formula:

=IF([Project Status Override]# <> "", [Project Status Override]#, IF(AND(COUNTIFS([Tasks Not Needed]:[Tasks Not Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0, Status:Status, "Completed") = COUNTIFS([Tasks Not Needed]:[Tasks Not Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0)), "Complete", IF(AND(COUNTIFS([Tasks Not Needed]:[Tasks Not Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0, Status:Status, "Not Started") = COUNTIFS([Tasks Not Needed]:[Tasks Not Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0)), "Planning", "Active")))

Screenshot: