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!
Best Answer
-
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))
Answers
-
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
-
Hey @Jen Lange , unfortunately not. This returns a total higher than 100% (in one of my columns it returned 1,100%)
-
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))
-
@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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!