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
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), \"//www.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}