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

Ruvalcaba12

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?

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"

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:

=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"

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.