Formula to total together and count a status & count completion

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"

Find more posts tagged with

Formulas

Accepted answers

jmyzk_cloudsmart_jp

@Dallas Garza

Congrats on figuring it out!😁

I did the same.

All comments

jmyzk_cloudsmart_jp

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

Dallas Garza

@jmyzk_cloudsmart_jp

So far so good, although there seems to be an issue when there are more grandparents.

As shown in the attached the formula that goes in the Status for the grandparent seems to be counting the Dones for the first grandparent

Dallas Garza

Figured it out.

For each "Status" of the grandparent I used:

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

jmyzk_cloudsmart_jp

@Dallas Garza

Congrats on figuring it out!😁

I did the same.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.