How do I add criteria to formula?

The smartsheet template I'm utilizing has a built-in formula for the status that is based off of the children...

=IF(COUNTIF(CHILDREN(), "Completed") = COUNT(CHILDREN()), "Completed", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Not Started") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Blocked") > 0, "In Progress")))))

For this particular project I have added the value "Nothing to Submit" to the Status dropdown list. How do I modify the existing formula to reference the parent as "Completed" even when the children's value is "Nothing to Submit"?

Any assistance is greatly appreciated!

Find more posts tagged with

Accepted answers

All comments

Lee Joramo

This should do the trick, add in another COUNTIF:

=IF(COUNTIF(CHILDREN(), "Completed") + COUNTIF(CHILDREN(), "Nothing to Submit") = COUNT(CHILDREN()), "Completed", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Not Started") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Blocked") > 0, "In Progress")))))

Here is a link to a demonstration sheet:

https://app.smartsheet.com/b/publish?EQBCT=d6ad1d4d2cd54777865452ce19a6d04e

Sara Ross

Thank you, Lee. The formula does indeed work, but is there a way to modify it so the parent specifies "Completed" when the children are either "Completed" or "Nothing to Submit"? Sorry I didn't include that criteria in my initial question.

Thanks!

Sara Ross

Above is the result using the initial formula.

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.