Formula help within a Sheet

Hello - we are having problems adjusting a formula to include additional details. The formula is being used on a sheet for a project checklist.

The parent formula shows the overall status of child tasks - if they are all "Completed" then the formula should say "Completed", if they all say "Not Started" then the formula says "Not Started", otherwise it says "In Progress".

I want to change this formula so that if a cell says "Cancelled" it counts towards "Completed" - if there are 5 child rows and 4 say "Completed" and 1 says "Cancelled" the formula should say "Completed". I've tried adding it in to the below but everything I do returns an error.

Here is the current formula:

=IF(COUNTIFS(CHILDREN(), @cell = "Completed") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <>"" )), "Completed", IF(COUNTIFS(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))

Thanks!

Find more posts tagged with

Formulas

Project Management

parent row

Child Rows

checklist

Accepted answers

Paul Newcome

Try this:

=IF(COUNTIFS(CHILDREN(), OR(@cell = "Completed", @cell = "Cancelled")) = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <>"" )), "Completed", IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Started", @cell = "Cancelled")) = COUNT(CHILDREN()), "Not Started", "In Progress"))

All comments

Nathan Slatton

Im not good with children but shouldnt it say @row not @cell?

Regina S

Thank you Nathan, we tried that and it didn't work.

Paul Newcome

Try this:

Regina S

@Paul Newcome thanks so much, that formula worked!

Paul Newcome

Happy to help. 👍️

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