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.

Archiduc

Hi @Laurenzella I have a formula of a similar type. My advice is that you use the AI tool to build this one. It is better equipped to build a complex nested if formula when you give it correct instructions. Try and then test the formula, then you can adjust your instructions if its not working as expected.

Laurenzella

I think it's too complicated to explain all the hidden columns and the default filter to the AI and there is no way to paste in the existing formula and ask it to add a new criteria (unless there is and I am using it incorrectly)

Genevieve P.

Hi @Laurenzella

To confirm, you're just looking to add in the criteria that "Cancelled" cells are the same as "Completed", right?

Try adding in this one bolded section:

=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, OR(@cell = "Completed", @cell = "Cancelled")) = 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")))

Cheers,
Genevieve