Trying to accurately gather % complete based on statuses and excluding N/A in children rows

Hi all!

I am revamping a project task list template that my team uses each time we are building a new location. We'd like a high level completion status for each department involved in these projects, but I'm running into a bit of a hiccup with my formula.

In the Status column of the departmental parent rows, I am using the following formula:

=COUNTIF(CHILDREN(Status@row), "Completed") / COUNT(CHILDREN([Task Name]@row))

However, I'm realizing that I need to exclude anything that has a status of "N/A" from my counts in order to project a correct % complete.

I have no idea if I'm just exhausted or what's going on, but I can't for the life of me adjust my Status bar formula to correctly count the completed items and divide them by the total number of tasks, excluding those that are N/A, to get an accurate %.

Any help is appreciated!

Find more posts tagged with

Sheets

Formulas

Reports

Project Management

Accepted answers

Jennifer Whitlock

After a lot of playing with it and trial and error, this seems to be working:

=SUM(COUNTIF(CHILDREN(Status@row), "Completed"), COUNTIF(CHILDREN(Status@row), "N/A")) / COUNT(CHILDREN([Task Name]@row))

All comments

Jen Lange

Hi @Jennifer Amador. Does this work for you?

=COUNTIF(CHILDREN(Status@row), "Completed") / (COUNT(CHILDREN([Task Name]@row)-COUNTIF(CHILDREN(Status@row), "N/A"))

-Jen

Jennifer Whitlock

https://community.smartsheet.com/discussion/comment/290208#Comment_290208

Hey @Jen Lange , unfortunately not. This returns a total higher than 100% (in one of my columns it returned 1,100%)

Jennifer Whitlock

After a lot of playing with it and trial and error, this seems to be working:

=SUM(COUNTIF(CHILDREN(Status@row), "Completed"), COUNTIF(CHILDREN(Status@row), "N/A")) / COUNT(CHILDREN([Task Name]@row))

Jen Lange

@Jennifer Amador. I'm glad you got it to work for you. I thought the order of the math might have needed to be adjusted, but I couldn't test my theory without stimulating the use case.

Thank you for your engagement and contribution to our community. Your inquiry is likely to assist other members across time AND it helps me flex my skills.

-Jen

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.