How to get the formulas to correctly count the completed tasks?

The current formula counts every task for the total.

=COUNTIF(DESCENDANTS([Planning Done]@row), 1) + " of " + COUNT(CHILDREN([Task/Topic]@row)) + " Done"

I have been trying to get it to only count when all subtask from its proper row have been completed.

There are 4 tasks and over 60 subtasks. When the subtasks are completed I want the 0 of 4 to accurately reflect that. Not count every single subtask towards it.

When I changed it from Descendants to Children it won't count at all.

=COUNTIF(CHILDREN([Planning Done]@row), 1) + " of " + COUNT(CHILDREN([Task/Topic]@row)) + " Done"

Is there something I'm missing?

Find more posts tagged with

Formulas

Accepted answers

Samuel Mueller

@Ruvalcaba12 try his

Create a helper column called "Helper" and put this column formula

=IF(COUNT(CHILDREN([Planning Done]@row)) = 0, "", IF(COUNT(CHILDREN([Planning Done]@row)) = COUNTIF(CHILDREN([Planning Done]@row), 1), 1, 0))

Then in your top level put this formula

=COUNTIFS(CHILDREN(Helper@row), 1) + " of " + COUNT(CHILDREN()) + " Done"

Samuel Mueller

@Ruvalcaba12 Maybe if you change the text of your second tier children to this or something similar

=IF(COUNT(CHILDREN([Planning Done]@row)) = COUNTIF(CHILDREN([Planning Done]@row), 1), "Complete", COUNTIFS(CHILDREN([Planning Done]@row), 1) + " of " + COUNT(CHILDREN([Task/Topic]@row)) + " Done")

Then your top tier would be

=COUNTIFS(CHILDREN(), "Complete") + " of " + COUNT(CHILDREN()) + " Done"

Samuel Mueller

You could keep the number but you have to change something to distinguish. Second Tier:

=IF(COUNT(CHILDREN([Planning Done]@row)) = COUNTIF(CHILDREN([Planning Done]@row), 1), COUNTIFS(CHILDREN([Planning Done]@row), 1) + " of " + COUNT(CHILDREN([Task/Topic]@row)) + " Complete", COUNTIFS(CHILDREN([Planning Done]@row), 1) + " of " + COUNT(CHILDREN([Task/Topic]@row)) + " Done")

Top tier:

=COUNTIFS(CHILDREN(), CONTAINS("Complete", @cell)) + " of " + COUNT(CHILDREN()) + " Done"

All comments

Samuel Mueller

@Ruvalcaba12 try his

Create a helper column called "Helper" and put this column formula

=IF(COUNT(CHILDREN([Planning Done]@row)) = 0, "", IF(COUNT(CHILDREN([Planning Done]@row)) = COUNTIF(CHILDREN([Planning Done]@row), 1), 1, 0))

Then in your top level put this formula

=COUNTIFS(CHILDREN(Helper@row), 1) + " of " + COUNT(CHILDREN()) + " Done"

Ruvalcaba12

@Samuel Mueller Thank you! I do have another question. Is there a way to correct that without a helper column?

Samuel Mueller

@Ruvalcaba12 Maybe if you change the text of your second tier children to this or something similar

=IF(COUNT(CHILDREN([Planning Done]@row)) = COUNTIF(CHILDREN([Planning Done]@row), 1), "Complete", COUNTIFS(CHILDREN([Planning Done]@row), 1) + " of " + COUNT(CHILDREN([Task/Topic]@row)) + " Done")

Then your top tier would be

=COUNTIFS(CHILDREN(), "Complete") + " of " + COUNT(CHILDREN()) + " Done"

Samuel Mueller

You could keep the number but you have to change something to distinguish. Second Tier:

Top tier:

=COUNTIFS(CHILDREN(), CONTAINS("Complete", @cell)) + " of " + COUNT(CHILDREN()) + " Done"

Samuel Mueller

Ruvalcaba12

@Samuel Mueller This is exactly what I was looking for. Thank you so much!

Ruvalcaba12

https://community.smartsheet.com/discussion/comment/386076#Comment_386076

It currently changes from 2 of 3 Done to 3 of 3 Complete when all the subtasks are done. I haven't found a way to make it so that stops using Complete and only use Done. Do you know what it is that prevents it from working when I change the Complete to Done in the formula?

Samuel Mueller

@Ruvalcaba12 so the reason this works is because they are different. Let me think about this if there is another way.

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.