Overall Total % completed

Grant Graves

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?

Paul Newcome

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) + "%"

Paul Newcome

Try replacing CHILDREN with DESCENDANTS.

Grant Graves

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) + "%"

Paul Newcome

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) + "%"

Grant Graves

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

Paul Newcome

Happy to help! 👍️

Anthony Barthelemy

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) + "%"

Paul Newcome

@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) + "%"