Overall Total % completed
Sorry If this is a repeat but the first post seemed to have disappeared.
My Goal is to have the Top row #1 Where that checkbox is to say what the total of completed of all the subtaks below. For example the Level 4 is showing 100% completed so the Top row should show 25% completed. But Not sure how to accomplish this?
Each level is calculating the tasks below it with this formula
=ROUND(IF(COUNTIF(CHILDREN(), 1) / COUNT(CHILDREN()) > 0, COUNTIF(CHILDREN(), 1) / COUNT(CHILDREN()), 0) * 100, 0) + "%"
Is this possible? to have the Overall Total show the Percentage overall? or do I need to change my approach to get this to calculate properly?
Best Answer

Hmm... Lets try excluding "text" which would be the data type of your current percentages.
=ROUND(IF(COUNTIFS(DESCENDANTS(), 1, DESCENDANTS(), NOT(ISTEXT(@cell))) / COUNTIFS(DESCENDANTS(), NOT(ISTEXT(@cell))) > 0, COUNTIFS(DESCENDANTS(), 1, DESCENDANTS(), NOT(ISTEXT(@cell))) / COUNTIFS(DESCENDANTS(), NOT(ISTEXT(@cell))), 0) * 100, 0) + "%"
Answers

Try replacing CHILDREN with DESCENDANTS.

That kind of worked but not giving me the result I need. It seems like it is counting the Rows Labeled Levels 1, Level 2 etc... so even tho each level is showing 100% the Grand total is only 97% any idea on fixing this?
The formula i put into the Total row is =ROUND(IF(COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()) > 0, COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()), 0) * 100, 0) + "%"

Hmm... Lets try excluding "text" which would be the data type of your current percentages.
=ROUND(IF(COUNTIFS(DESCENDANTS(), 1, DESCENDANTS(), NOT(ISTEXT(@cell))) / COUNTIFS(DESCENDANTS(), NOT(ISTEXT(@cell))) > 0, COUNTIFS(DESCENDANTS(), 1, DESCENDANTS(), NOT(ISTEXT(@cell))) / COUNTIFS(DESCENDANTS(), NOT(ISTEXT(@cell))), 0) * 100, 0) + "%"

Thank you Paul you sir are a truly amazing life saver!!

Greeting Paul:
Can you Post the Final Formula that worked? I used the last posted Formula and I have the same Issue:
Previous Formula:
That kind of worked but not giving me the result I need. It seems like it is counting the Rows Labeled Levels 1, Level 2 etc... so even tho each level is showing 100% the Grand total is only 97% any idea on fixing this?
The formula i put into the Total row is =ROUND(IF(COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()) > 0, COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()), 0) * 100, 0) + "%"

@Ray B Did you try the formula in the "Accepted Answer" at the top of the thread?
=ROUND(IF(COUNTIFS(DESCENDANTS(), 1, DESCENDANTS(), NOT(ISTEXT(@cell))) / COUNTIFS(DESCENDANTS(), NOT(ISTEXT(@cell))) > 0, COUNTIFS(DESCENDANTS(), 1, DESCENDANTS(), NOT(ISTEXT(@cell))) / COUNTIFS(DESCENDANTS(), NOT(ISTEXT(@cell))), 0) * 100, 0) + "%"
Help Article Resources
Categories
Check out the Formula Handbook template!