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:

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!

• ✭✭✭✭✭

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))

• ✭✭✭✭✭

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!