Which is the most efficient formula in this case with multiple conditions?

Arundhati

I want to set up a formula that reflects Status in the Parent row. Below are the conditions:

If all CHILDREN tasks are Not Started, then PARENT status is Not Started

If any CHILDREN task is In Progress or Completed, then PARENT status is In Progress

If all CHILDREN tasks are Completed, then PARENT status is Completed

I don't know where to begin. My nested IF statements are getting too long and complicated.

Thank you!

Find more posts tagged with

Formulas

Accepted answers

All comments

Razetto

@Arundhati On the parent row I enter the following formula, the range depends on how many children you have so say that there are only children rows 2-5, then: =IF(COUNTIF(Status2:Status5, @cell = "Completed") = COUNT(Status2:Status5), "Completed", IF(COUNTIF(Status2:Status5, @cell = "Not Started") = COUNT(Status2:Status5), "Not Started", "In Progress"))

Arundhati

Thanks Razetto. It is returning "In progress" as the response even if all children tasks are Completed. Can you replace @cell with @row in this formula? And could you also explain in words how the formula would read in Smartsheet? Thank you

Arundhati

I found another post with the same question: https://community.smartsheet.com/discussion/105056/formula-for-a-parent-row-to-populate-status-based-on-children-status#:~:text=Here's%20how%20the%20formula%20works,status%20as%20%22Not%20Started%22.

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.