Formula to total together and count a status & count completion

Dallas Garza · 11/09/23

Hard to title...

But refer to the attached screenshots.

On the parent task right now I have it counting the amount of child tasks' status (the progress bar).

As you can see under the expanded task of "External Kick off" there are 5 other tasks. I have set them as Full on the status bar as shown and so the "External Kick off" is showing the 5 of 5 Done.

What I would like is for the parent "Discover" to be able to count the Full under it and the others that are X of Y. As in on the "Discover"'s status it should be saying 2 of 7 Done because the "External Kick off" was completed 5 of 5 Done and the line right under it is Full.

The formula I have in the status for "Discover" is currently:

=COUNTIF(CHILDREN(Status@row), "Full") + " of " + COUNT(CHILDREN([Task Name]@row)) + " Done"

jmyzk_cloudsmart_jp · 11/10/23

@Dallas Garza

Congrats on figuring it out!😁

I did the same.

jmyzk_cloudsmart_jp · 11/10/23

Hi @Dallas Garza

I added helper columns, "Anc", "isParent", and "isDone".

"Anc", and "isParent" are to check if the row is parent or stand-alone (meaning, with no children nor parent except the Grand Parent.)

The formula for isDone is;

=IF(AND(ANC@row = 1, isParent@row = 0), IF(Status@row = "Full", 1, 0), IF(AND(ANC@row = 1, isParent@row = 1), IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(Status@row), "Full"), 1, 0)))

The formula means that if the row is stand-alone, then check if the status is full; otherwise, if the row is parent, check if the number of children equals the number of full children. Then, if yes, 1 or true.

The published demo sheet below is editable in the status column. So, you can check how the formula works by changing the status.😀

https://app.smartsheet.com/b/publish?EQBCT=60f437ff9e554654bee2eba09fe773fd