Status Formula
I need some help fine-tuning this formula. I need it to follow the following rules. Open to better options!!!
It would need to be on a parent row. It will summarize the children's status.
Rules:
If all are 'Not Started' then the summary should show 'Not Started'.
If all are 'Completed' then the summary should show 'Completed'.
If all are 'On Hold' then the summary should show 'On Hold'.
If all are 'Cancelled' then the summary should show 'Cancelled'.
If even 1 is 'In Progress' then the summary should show 'In Progress'
**The formula below is working except 'Not Started' is trumping the 'In Progress'. **
=IF(COUNT(DESCENDANTS()) = COUNTIFS((DESCENDANTS()), OR(@cell = "Not Started", @cell = " ")), "Not Started", IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Canceled"), "Canceled", IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "On Hold"), "On Hold", IF(OR(CONTAINS("In Progress", CHILDREN()), AND(COUNTIF(CHILDREN(), "Complete") > 0, COUNTIF(CHILDREN(), "On Hold") > 0)), "In Progress", IF(OR(CONTAINS("Not Started", CHILDREN()), AND(COUNTIF(CHILDREN(), "") > 0, COUNTIF(CHILDREN(), "On Hold") > 0)), "Not Started", IF(OR(CONTAINS(ISBLANK(), CHILDREN()), AND(COUNTIF(CHILDREN(), "Not Started") > 0, COUNTIF(CHILDREN(), "On Hold") > 0)), "Not Started")))))))
Best Answer
-
Paul Newcome ✭✭✭✭✭✭Give this a try:
=IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Started", @cell = "")) = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Not Started", IF(COUNTIFS(CHILDREN(), @cell = "Complete") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "On Hold") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "On Hold", IF(COUNTIFS(CHILDREN(), @cell = "Cancelled") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Cancelled", "In Progress"))))
thinkspi.com
Answers
-
Paul Newcome ✭✭✭✭✭✭
Give this a try:
=IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Started", @cell = "")) = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Not Started", IF(COUNTIFS(CHILDREN(), @cell = "Complete") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "On Hold") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "On Hold", IF(COUNTIFS(CHILDREN(), @cell = "Cancelled") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Cancelled", "In Progress"))))
thinkspi.com
-
RDill ✭
Thank you Paul!!!!
-
Paul Newcome ✭✭✭✭✭✭
Help Article Resources
Categories
Appreciate the fresh pair of eyes<\/p>
Best,<\/p>
Pierre-Eric<\/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":107557,"type":"question","name":"sum up values in a different columns if another dropdown column contains the required values","excerpt":"I have a dropdown column that contains these values, A1-A9, B1-B9, C1-C9, D1-D9, E1-E9,S1-S9. These values represent the number of people, I can have a cell in the column with value A1, C1 &D3. I also have 6 columns that calculate the decimals I need. Now I want to be able to check the dropdown column and add the values. I…","snippet":"I have a dropdown column that contains these values, A1-A9, B1-B9, C1-C9, D1-D9, E1-E9,S1-S9. These values represent the number of people, I can have a cell in the column with…","categoryID":322,"dateInserted":"2023-07-13T14:38:38+00:00","dateUpdated":null,"dateLastComment":"2023-07-13T19:36:52+00:00","insertUserID":163437,"insertUser":{"userID":163437,"name":"Tadiwa","url":"https:\/\/community.smartsheet.com\/profile\/Tadiwa","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-13T19:35:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":163437,"lastUser":{"userID":163437,"name":"Tadiwa","url":"https:\/\/community.smartsheet.com\/profile\/Tadiwa","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-13T19:35:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":20,"score":null,"hot":3378538530,"url":"https:\/\/community.smartsheet.com\/discussion\/107557\/sum-up-values-in-a-different-columns-if-another-dropdown-column-contains-the-required-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107557\/sum-up-values-in-a-different-columns-if-another-dropdown-column-contains-the-required-values","format":"Rich","lastPost":{"discussionID":107557,"commentID":385080,"name":"Re: sum up values in a different columns if another dropdown column contains the required values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385080#Comment_385080","dateInserted":"2023-07-13T19:36:52+00:00","insertUserID":163437,"insertUser":{"userID":163437,"name":"Tadiwa","url":"https:\/\/community.smartsheet.com\/profile\/Tadiwa","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-13T19:35:57+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-07-13T19:37:02+00:00","dateAnswered":"2023-07-13T19:31:48+00:00","acceptedAnswers":[{"commentID":385073,"body":"
You would need to write out separate SUMIFS for each of the lettered columns. Once you get those written out, you would add them together as need to get your combined counts.<\/p>
<\/p>
=SUMIFS({A Column}, {Managers Column}, OR(HAS(@cell, \"A1\"), HAS(@cell, \"A2\"), HAS(@cell, \"A3\")))<\/p>
<\/p>
=SUMIFS({A Column}, {Managers Column}, OR(HAS(@cell, \"A1\"), HAS(@cell, \"A2\"), HAS(@cell, \"A3\"))) + SUMIFS({B Column}, {Managers Column}, OR(HAS(@cell, \"B1\"), HAS(@cell, \"B2\"), HAS(@cell, \"B3\")))<\/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":107562,"type":"question","name":"Find Duplicate Values in one Column","excerpt":"I looked and cannot find a solution, I have employees who are signing up for a course and some are doing multiples for various reasons (not all legitimate) and I would like to find out how to see who has multiple entries so I can delete the ones that should not be duplicated like with excel where I can highlight but…","snippet":"I looked and cannot find a solution, I have employees who are signing up for a course and some are doing multiples for various reasons (not all legitimate) and I would like to…","categoryID":322,"dateInserted":"2023-07-13T15:38:38+00:00","dateUpdated":null,"dateLastComment":"2023-07-13T16:21:06+00:00","insertUserID":163388,"insertUser":{"userID":163388,"name":"Dpres","title":"Clinical Manager","url":"https:\/\/community.smartsheet.com\/profile\/Dpres","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-13T17:00:32+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":163388,"lastUser":{"userID":163388,"name":"Dpres","title":"Clinical Manager","url":"https:\/\/community.smartsheet.com\/profile\/Dpres","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-13T17:00:32+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":23,"score":null,"hot":3378529184,"url":"https:\/\/community.smartsheet.com\/discussion\/107562\/find-duplicate-values-in-one-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107562\/find-duplicate-values-in-one-column","format":"Rich","lastPost":{"discussionID":107562,"commentID":385051,"name":"Re: Find Duplicate Values in one Column","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385051#Comment_385051","dateInserted":"2023-07-13T16:21:06+00:00","insertUserID":163388,"insertUser":{"userID":163388,"name":"Dpres","title":"Clinical Manager","url":"https:\/\/community.smartsheet.com\/profile\/Dpres","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-13T17:00:32+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-07-13T16:20:49+00:00","dateAnswered":"2023-07-13T15:44:56+00:00","acceptedAnswers":[{"commentID":385039,"body":"
If you set this as a column formula in your helper column, it will return the number of times each email is duplicated. You could also setup conditional formatting to highlight that row if the result is greater than 1.<\/p>
=COUNTIFS([Employee Email]:[Employee Email], [Employee Email]@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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
