Question on status rollup using IF COUNTIF formula

I have 4 statuses (Not Started, In Progress, Complete and N/A). I really want the N/A to either not be considered in the rollup, or to be treated like 'Complete.' No matter what I do, I keep getting an unparseable error. This is the latest version of what I have:

=IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", 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(), "N/A") > 0, "Complete", )))))

Any suggestions on how to get this to work?

Find more posts tagged with

Accepted answers

Paul Newcome

The initial issue is that the comma after "Complete" at the end needs removed.

You could also have it count the same as "Complete" by using an OR function like so:

=IF(COUNTIF(CHILDREN(), OR(@cell = "Complete", @cell = "N/A")) = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))

All comments

Paul Newcome

The initial issue is that the comma after "Complete" at the end needs removed.

You could also have it count the same as "Complete" by using an OR function like so:

=IF(COUNTIF(CHILDREN(), OR(@cell = "Complete", @cell = "N/A")) = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))

larak

Thank you @Paul Newcome !

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