Using IF, OR, and CONTAINS, ISBLANK to track status of actions

Hello, I'm attempting to autofill the primary row for each project with subtasks under each project. The status of subtasks vary from "Not Started", "In Progress", "Outstanding", "Complete".

I'm trying to create a formula that examines the set of rows' status under the project to summarize the overall status of the project. I'm trying to simplify it by being either "In Progress" or "Complete"

Is there a way to do this?

=IF(OR(CONTAINS("In Progress", [Status of Actions]71:[Status of Actions]76), CONTAINS(ISBLANK([Status of Actions]71:[Status of Actions]76, [Status of Actions]71:[Status of Actions]76))), "In Progress", "Complete")

Find more posts tagged with

Accepted answers

Mary_A

This will allow the parent task to be "Not Started", "Complete", or "In Progress"

=IF(COUNT(CHILDREN(Task@row)) = COUNTIF(CHILDREN(Status@row), "Complete"), "Complete", IF(COUNT(CHILDREN(Task@row)) = COUNTIF(CHILDREN(Status@row), "Not Started"), "Not Started", "In Progress"))

All comments

SJ Sellers

What about this?

If the total number of subtasks (determined by counting the number of values in the "Primary Column", in this case Task) is equal to the number of rows where the status is "Complete", then the parent row is "Complete", else it is "In Progress".

=IF(COUNT(CHILDREN(Task@row)) = COUNTIF(CHILDREN(Status@row), "Complete"), "Complete", "In Progress")

lek123

@SJ Sellers Brilliant, this seems to work. Never thought about using the CHILDREN function. Thank you so much! For now this seems to be great, wondering if there would be a way of having another option for "Not Started"?

Mary_A

This will allow the parent task to be "Not Started", "Complete", or "In Progress"

=IF(COUNT(CHILDREN(Task@row)) = COUNTIF(CHILDREN(Status@row), "Complete"), "Complete", IF(COUNT(CHILDREN(Task@row)) = COUNTIF(CHILDREN(Status@row), "Not Started"), "Not Started", "In Progress"))

lek123

Hi @Mary_A Thank you!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.