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?
Best Answers
-
Samuel Mueller ✭✭✭✭✭✭@Ruvalcaba12try 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 ✭✭✭✭✭✭
@Ruvalcaba12Maybe 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"
Answers
-
Samuel Mueller ✭✭✭✭✭✭
@Ruvalcaba12try 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 MuellerThank you! I do have another question. Is there a way to correct that without a helper column?
-
Samuel Mueller ✭✭✭✭✭✭
@Ruvalcaba12Maybe 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 ✭✭✭✭✭✭
-
@Samuel MuellerThis is exactly what I was looking for. Thank you so much!
-
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 ✭✭✭✭✭✭
@Ruvalcaba12so the reason this works is because they are different. Let me think about this if there is another way.
Help Article Resources
Categories
In your sheet, create a third section that adds the results of the first two. It would have the same rows, but the formula for each row would add the cells of section 1 + section 2.<\/p>
Add a helper column called ShowInReport as a Check Box and check all the rows in the third section. <\/p>
Create a report and tell it to show you only Section 3 (where the ShowInReport is true.<\/p>
I hope that makes sense.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108864,"type":"question","name":"Is there a formula to show as %","excerpt":"What I am trying to do is have my overview brought in automatically each month so it doesn't have to manually be typed in. The struggle I'm having is it wants to bring in as a decimal instead of a %. Below is the formula I am currently using. Is there a way to make it show as % =\"We are at \" + [% closed rate]@row + \"…","snippet":"What I am trying to do is have my overview brought in automatically each month so it doesn't have to manually be typed in. The struggle I'm having is it wants to bring in as a…","categoryID":322,"dateInserted":"2023-08-11T17:22:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T19:29:15+00:00","insertUserID":144360,"insertUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T20:24:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":144360,"lastUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T20:24:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":36,"score":null,"hot":3383558496,"url":"https:\/\/community.smartsheet.com\/discussion\/108864\/is-there-a-formula-to-show-as","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108864\/is-there-a-formula-to-show-as","format":"Rich","lastPost":{"discussionID":108864,"commentID":390301,"name":"Re: Is there a formula to show as %","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390301#Comment_390301","dateInserted":"2023-08-11T19:29:15+00:00","insertUserID":144360,"insertUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T20:24:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T19:29:56+00:00","dateAnswered":"2023-08-11T17:49:58+00:00","acceptedAnswers":[{"commentID":390269,"body":"
Try this - =\"We are at \" + [% closed rate]@row * 100 + \"% closed rate on ticket status for the month of \"+[Month]@row.<\/p>"},{"commentID":390301,"body":"
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//m.santa-greenland.com/community/discussion/107817/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":59,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"
![https:\/\/us.v-cdn.net\/6031209\/uploads\/6GLGYZS8A9J2\/image.png<\/a><\/p>","bodyRaw":"[{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/6GLGYZS8A9J2\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/6GLGYZS8A9J2\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n\\n\\n\"}]","format":"rich","dateInserted":"2023-07-19T19:38:33+00:00","insertUser":{"userID":137019,"name":"Samuel Mueller","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Samuel%20Mueller","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B9G8B70WXQWC\/nKTI46EH9WQOM.jpg","dateLastActive":"2023-08-11T18:30:19+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386076#Comment_386076","embedType":"quote"}">](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/6GLGYZS8A9J2\/image.png\"){kind=link}