Parent Status based on Children Harvy ball status.

Dear team,

I am trying to build a status update tracker, for teams to update each milestone. They are expected to update each milestone using the harvey ball status (0% /25% /50 % /75% /100%). I am trying to automate the status of parent based on children's Harvey ball status as below with logic as " If the status of all children in 0% the parent status will return as " Not started". If all the children are 100% the parent will return as " Completed". If the children has any other status individually the parent will return as " In progress"

=IF(COUNTIF(CHILDREN(),<0.25)=COUNT(CHILDREN()),"Not Started",IF(COUNTIF(CHILDREN(),=1)=COUNT(CHILDREN()),"Completed","In Progress")))

The above formula is returning #UNPARSEABLE". Can you please help me what is the error i am making here?. I am a newbie on smartsheets. you help is highly appreciated.

Picture1.png


Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭
    Answer ✓

    With the symbols you are using, the options would typically be represented by the text entries [Empty, Quarter, Half, Three Quarter, Full]. In this case, this should work:

    =IF(COUNTIF(CHILDREN([BallCell]@row), "Empty") = COUNT(CHILDREN([BallCell]@row)), "Not Started", IF(COUNTIF(CHILDREN([BallCell]@row), "Full") = COUNT(CHILDREN([BallCell]@row)), "Completed", "In Progress"))

    If you do have your sheet setup somehow so that the entries are respresented by [0, 0.25, 0.50, 0.75, 1], then this should work:

    =IF(COUNTIF(CHILDREN([BallCell]@row), < 0.25) = COUNT(CHILDREN([BallCell]@row)), "Not Started", IF(COUNTIF(CHILDREN([BallCell]@row), 1) = COUNT(CHILDREN([BallCell]@row)), "Completed", "In Progress"))

    In either case, you will need to substitute [BallCell] with the name of the column with your symbols. The column name is not visible in your screenshot.

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭
    Answer ✓

    With the symbols you are using, the options would typically be represented by the text entries [Empty, Quarter, Half, Three Quarter, Full]. In this case, this should work:

    =IF(COUNTIF(CHILDREN([BallCell]@row), "Empty") = COUNT(CHILDREN([BallCell]@row)), "Not Started", IF(COUNTIF(CHILDREN([BallCell]@row), "Full") = COUNT(CHILDREN([BallCell]@row)), "Completed", "In Progress"))

    If you do have your sheet setup somehow so that the entries are respresented by [0, 0.25, 0.50, 0.75, 1], then this should work:

    =IF(COUNTIF(CHILDREN([BallCell]@row), < 0.25) = COUNT(CHILDREN([BallCell]@row)), "Not Started", IF(COUNTIF(CHILDREN([BallCell]@row), 1) = COUNT(CHILDREN([BallCell]@row)), "Completed", "In Progress"))

    In either case, you will need to substitute [BallCell] with the name of the column with your symbols. The column name is not visible in your screenshot.

  • Thanks a million Carson!. Formula works like charm!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
@v.winters<\/a> <\/p>

If the two percent columns are formatted as percent columns (see screenshot), then the % Not Complete column would have this formula: <\/p>

=1 - [%Complete]<\/em>@row<\/p>

\n
\n \n \"Menu.PNG\"<\/img><\/a>\n <\/div>\n<\/div>\n

Hope this helps!<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107009,"type":"question","name":"I need help with a if formula to get the $ amount total if another column = a specific item.","excerpt":"I want to make a formula in the Column \"A Total\" in the highlighted Cell, that will give me the Sum of $ from Grand Total Column if the A is in the Project column.","snippet":"I want to make a formula in the Column \"A Total\" in the highlighted Cell, that will give me the Sum of $ from Grand Total Column if the A is in the Project column.","categoryID":322,"dateInserted":"2023-06-28T12:55:48+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T16:37:28+00:00","insertUserID":162272,"insertUser":{"userID":162272,"name":"Elayne Smith","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Elayne%20Smith","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!z_neMx7Hvj4!mVDfnuqtMWQ!RH9Ly5uQ-po","dateLastActive":"2023-06-28T13:41:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-28T17:08:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":35,"score":null,"hot":3375929596,"url":"https:\/\/community.smartsheet.com\/discussion\/107009\/i-need-help-with-a-if-formula-to-get-the-amount-total-if-another-column-a-specific-item","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107009\/i-need-help-with-a-if-formula-to-get-the-amount-total-if-another-column-a-specific-item","format":"Rich","lastPost":{"discussionID":107009,"commentID":382888,"name":"Re: I need help with a if formula to get the $ amount total if another column = a specific item.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382888#Comment_382888","dateInserted":"2023-06-28T16:37:28+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-28T17:08:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/N80WFJ95P6OH\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-28T13:45:50+00:00","dateAnswered":"2023-06-28T13:05:16+00:00","acceptedAnswers":[{"commentID":382818,"body":"

Hi @Elayne Smith<\/a>,<\/p>

The formula for this would be:<\/p>

=SUMIF(Project:Project, \"A\", [Grand Total]:[Grand Total])<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Hope this helps - any issues etc. then just post! 😊<\/span><\/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":107018,"type":"question","name":"ROI Calculation Formula","excerpt":"I have a sheet that gathers numbers for various project costs and for various values. There are two columns that sum each of these; Total Costs and Total Value. I am using the following formula to calculate the ROI: =([Total Value]@row - [Total Costs]@row) \/ [Total Costs]@row * 100 However, the calculation seems to be off.…","snippet":"I have a sheet that gathers numbers for various project costs and for various values. There are two columns that sum each of these; Total Costs and Total Value. I am using the…","categoryID":322,"dateInserted":"2023-06-28T14:24:22+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T15:14:39+00:00","insertUserID":159640,"insertUser":{"userID":159640,"name":"Katherine Simpson","url":"https:\/\/community.smartsheet.com\/profile\/Katherine%20Simpson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/EY1XYADLUP90\/n8693BY7J2F39.jpg","dateLastActive":"2023-06-28T15:13:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":159640,"lastUser":{"userID":159640,"name":"Katherine Simpson","url":"https:\/\/community.smartsheet.com\/profile\/Katherine%20Simpson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/EY1XYADLUP90\/n8693BY7J2F39.jpg","dateLastActive":"2023-06-28T15:13:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":26,"score":null,"hot":3375928741,"url":"https:\/\/community.smartsheet.com\/discussion\/107018\/roi-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107018\/roi-calculation-formula","format":"Rich","lastPost":{"discussionID":107018,"commentID":382858,"name":"Re: ROI Calculation Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382858#Comment_382858","dateInserted":"2023-06-28T15:14:39+00:00","insertUserID":159640,"insertUser":{"userID":159640,"name":"Katherine Simpson","url":"https:\/\/community.smartsheet.com\/profile\/Katherine%20Simpson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/EY1XYADLUP90\/n8693BY7J2F39.jpg","dateLastActive":"2023-06-28T15:13:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-28T15:14:06+00:00","dateAnswered":"2023-06-28T14:56:38+00:00","acceptedAnswers":[{"commentID":382852,"body":"

Try omitting the \"*100\" at the end of your formula. Convert the column to show the number as a percentage. <\/p>

\n
\n \n \"Screenshot<\/img><\/a>\n <\/div>\n<\/div>\n


<\/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":[]}">

Trending in Formulas and Functions