Trying to determine if all children have N/A Status

ro.fei
ro.fei ✭✭✭✭✭
inFormulas and Functions

I'm working on a formula for summary rows to look at the % Complete of the row as well as the statuses of the child rows. I have the % Complete portion handled:

=IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row > 0, [% Complete]@row < 1), "In Progress", IF([% Complete]@row = 0, "Not Started")))

I want to add in another nested IF to check the statuses of the child rows, & if they'reallmarked N/A, then display N/A. Something like this:

=IF((All children contain N/A), "N/A", IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row > 0, [% Complete]@row < 1), "In Progress", IF([% Complete]@row = 0, "Not Started"))))

I just need help writing out a logical statement that will check the status of the child rows. Let me know if I need to clarify anything & thanks for your help!!

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. This will make it easier for others to find a solution or help to answer!

Tags:
· Share on Facebook Share on Twitter

Best Answer

  • ro.fei
    ro.fei ✭✭✭✭✭
    Answer ✓

    I got it! It's long but it works:


    =IF(AND(CONTAINS("N/A", CHILDREN([email protected])), CONTAINS("Not Started", CHILDREN([email protected])) = false, CONTAINS("In Progress", CHILDREN([email protected])) = false, CONTAINS("Complete", CHILDREN([email protected])) = false), "N/A", IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row > 0, [% Complete]@row < 1), "In Progress", IF([% Complete]@row = 0, "Not Started"))))

    ✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. This will make it easier for others to find a solution or help to answer!

    · Share on Facebook Share on Twitter

Answers

  • ro.fei
    ro.fei ✭✭✭✭✭
    Answer ✓

    I got it! It's long but it works:


    =IF(AND(CONTAINS("N/A", CHILDREN([email protected])), CONTAINS("Not Started", CHILDREN([email protected])) = false, CONTAINS("In Progress", CHILDREN([email protected])) = false, CONTAINS("Complete", CHILDREN([email protected])) = false), "N/A", IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row > 0, [% Complete]@row < 1), "In Progress", IF([% Complete]@row = 0, "Not Started"))))

    ✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. This will make it easier for others to find a solution or help to answer!

    · Share on Facebook Share on Twitter

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公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
Does this work?<\/p>
\n
\n \n \"image.png\"<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108205,"type":"question","name":"Either checkbox column to contribute to % complete","excerpt":"I am looking for a formula that will reference two checkbox columns to calculate a % complete if either is checked. I'd also like this formula to calculate an average % complete based on the % complete of the child tasks. This was working correctly when I was using only one checkbox column, but I am struggling to…","snippet":"I am looking for a formula that will reference two checkbox columns to calculate a % complete if either is checked. I'd also like this formula to calculate an average % complete…","categoryID":322,"dateInserted":"2023-07-27T17:53:55+00:00","dateUpdated":null,"dateLastComment":"2023-07-27T19:21:53+00:00","insertUserID":164195,"insertUser":{"userID":164195,"name":"Rachel Kelly","url":"https:\/\/community.smartsheet.com\/profile\/Rachel%20Kelly","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-27T19:00:06+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-07-28T00:33:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":34,"score":null,"hot":3380967948,"url":"https:\/\/community.smartsheet.com\/discussion\/108205\/either-checkbox-column-to-contribute-to-complete","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108205\/either-checkbox-column-to-contribute-to-complete","format":"Rich","lastPost":{"discussionID":108205,"commentID":387648,"name":"Re: Either checkbox column to contribute to % complete","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387648#Comment_387648","dateInserted":"2023-07-27T19:21:53+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-07-28T00:33: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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/V4HP6H7XE3I9\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-27T19:01:16+00:00","dateAnswered":"2023-07-27T18:58:57+00:00","acceptedAnswers":[{"commentID":387623,"body":"

Try this:<\/p>

=IF(COUNT(CHILDREN()) = 0, IF(OR(Done@row = 1, [N\/A]@row = 1), 1, 0), AVG(CHILDREN()))<\/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":108195,"type":"question","name":"COUNTIF Function Not Working Correctly","excerpt":"Hello, I have a COUNTIF function I am using to count all cells that are 'Blank' in a column. We should have one result, but the formula is showing as zero. Any thoughts on this one? =COUNTIF([Account Dashboard]:[Account Dashboard], \" \")","snippet":"Hello, I have a COUNTIF function I am using to count all cells that are 'Blank' in a column. We should have one result, but the formula is showing as zero. Any thoughts on this…","categoryID":322,"dateInserted":"2023-07-27T16:41:32+00:00","dateUpdated":null,"dateLastComment":"2023-07-27T19:11:08+00:00","insertUserID":164192,"insertUser":{"userID":164192,"name":"ewitt19205","url":"https:\/\/community.smartsheet.com\/profile\/ewitt19205","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mo_vH-5dWnI!5OcveUI6Zjo!UtWovaSRry_","dateLastActive":"2023-07-27T19:07:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164192,"lastUser":{"userID":164192,"name":"ewitt19205","url":"https:\/\/community.smartsheet.com\/profile\/ewitt19205","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mo_vH-5dWnI!5OcveUI6Zjo!UtWovaSRry_","dateLastActive":"2023-07-27T19:07:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":31,"score":null,"hot":3380962360,"url":"https:\/\/community.smartsheet.com\/discussion\/108195\/countif-function-not-working-correctly","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108195\/countif-function-not-working-correctly","format":"Rich","lastPost":{"discussionID":108195,"commentID":387638,"name":"Re: COUNTIF Function Not Working Correctly","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387638#Comment_387638","dateInserted":"2023-07-27T19:11:08+00:00","insertUserID":164192,"insertUser":{"userID":164192,"name":"ewitt19205","url":"https:\/\/community.smartsheet.com\/profile\/ewitt19205","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mo_vH-5dWnI!5OcveUI6Zjo!UtWovaSRry_","dateLastActive":"2023-07-27T19:07:23+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-27T19:10:56+00:00","dateAnswered":"2023-07-27T16:47:29+00:00","acceptedAnswers":[{"commentID":387588,"body":"

Hi @ewitt19205<\/a> <\/p>

Try removing the space between the quotes. You are counting all cells with 1 space in the cell. Blanks are counted with the quotes together.<\/p>

When counting blanks though, its also good to use a COUNTIFS and compare the blank off another column that always has a value. Like Countifs when the Project name is not blank (<>\"//www.santa-greenland.com/community/discussion/106137/\") and the Account Dashboard is blank (\"//www.santa-greenland.com/community/discussion/106137/\").<\/p>

I hope that helps.<\/p>

Thanks,<\/p>

Matt<\/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

  • COUNTIF and OR or IF Formula

    I'm looking to use COUNTIF to calculate if something is the right date and then if it has meets one of the following criteria (using references). My current formula looks like this: =COUNTIFS({Despatch Report Date}, =Date8, {Despatch Report Lot Status}, "Q", {Despatch Report WO Status}, <99 I basically want to count if its…

  • Either checkbox column to contribute to % complete

    I am looking for a formula that will reference two checkbox columns to calculate a % complete if either is checked. I'd also like this formula to calculate an average % complete based on the % complete of the child tasks. This was working correctly when I was using only one checkbox column, but I am struggling to…

  • COUNTIF Function Not Working Correctly

    Hello, I have a COUNTIF function I am using to count all cells that are 'Blank' in a column. We should have one result, but the formula is showing as zero. Any thoughts on this one? =COUNTIF([Account Dashboard]:[Account Dashboard], " ")