Project Status Formula and Checklist items

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:

Find more posts tagged with

Formulas

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.