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

Ruvalcaba12
Ruvalcaba12 ✭✭✭
edited 07/19/23 in Formulas and Functions
ScreenShot.png

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?

Tags:
· Share on FacebookShare on Twitter

Best Answers

  • Samuel Mueller
    Samuel Mueller Overachievers
    edited 07/19/23 Answer ✓

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

    · Share on FacebookShare on Twitter
  • Samuel Mueller
    Samuel Mueller Overachievers
    Answer ✓

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

    · Share on FacebookShare on Twitter
  • Samuel Mueller
    Samuel Mueller Overachievers
    Answer ✓

    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"

    · Share on FacebookShare on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!