Project Status Formula and Checklist items

Laurenzella

We have a project checklist template that is provisioned via Control Center. There are hidden columns (the "Task Not Needed" referenced in the formula) that show only the checklist for the Project Type selected in intake - the tasks that are not supposed to appear are checked because all the project-specific checklists live in this master template.

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".

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:

Laurenzella

ETA: the items show up as crossed out in the above because the "tasks not needed" box is checked off in the hidden column. The tasks uncheck based on the project type chosen at intake when the Default filter is on (Default is to show only unchecked items). This is the sheet with the filter on - since none of the tasks are marked as "Needed" in this template it shows as complete.