sumif with children and checkboxes
I'm trying to create a setup where I sum together only the children if another box for that row is checked.
I've come up with (see screenshot below as well):
=SUMIF(CHILDREN(Include),1,CHILDREN())
But despite trying different variations of the formula I get everything back as #UNPARSEABLE
I appreciate any help you can offer!
Comments
-
The ultimate goal is that I can prepare a quote sheet for a client with all their options then we can eliminate options to help the budget and our grand total will show the revised numbers, with the check boxes (or unchecked boxes) showing what we've cut to get there)
-
Andrée Starå ✭✭✭✭✭✭Hi Danny,
Here are the formulas so that you can easily copy them.
=SUMIF(CHILDREN(Include1); "1"; (CHILDREN(Estimate1)))
=SUMIF(CHILDREN(Include2); "1"; (CHILDREN(Estimate2)))
Please see attached link/screenshot.
I hope this helps you!
Have a fantastic weekend!
Best,
Andrée Starå - Workflow Consultant / Get Done
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Awesome! Thank you so much - I knew I was close
In my case for the first line I modified the formula to:=SUMIF(CHILDREN(Include10), "1", (CHILDREN(Estimate10)))
With the number 10 indicating what line the parent was on (just for reference to anyone else who stumbles upon this.Thanks again!
-
Andrée Starå ✭✭✭✭✭✭
Happy to help and your welcome!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
Try: =IF(OR(ISBLANK(Title@row), Title@row = \"Resource\"), 0, 3)<\/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":111329,"type":"question","name":"IF Statements and Summing Children","excerpt":"Hi there - I need a hand with a formula. I'm trying to do the following: Show the difference between Budgeted Amount and Quote Amount unless there is a value in the Actual Amount column, then I'd like to show the difference between the Budgeted Amount and the Actual Amount. But I only want to calculate those values if the…","snippet":"Hi there - I need a hand with a formula. I'm trying to do the following: Show the difference between Budgeted Amount and Quote Amount unless there is a value in the Actual Amount…","categoryID":322,"dateInserted":"2023-10-06T16:41:19+00:00","dateUpdated":null,"dateLastComment":"2023-10-06T19:01:55+00:00","insertUserID":159342,"insertUser":{"userID":159342,"name":"Beth Fantozzi 1","url":"https:\/\/community.smartsheet.com\/profile\/Beth%20Fantozzi%201","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-06T19:37:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":159342,"lastUser":{"userID":159342,"name":"Beth Fantozzi 1","url":"https:\/\/community.smartsheet.com\/profile\/Beth%20Fantozzi%201","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-06T19:37:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":32,"score":null,"hot":3393230594,"url":"https:\/\/community.smartsheet.com\/discussion\/111329\/if-statements-and-summing-children","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111329\/if-statements-and-summing-children","format":"Rich","lastPost":{"discussionID":111329,"commentID":398969,"name":"Re: IF Statements and Summing Children","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398969#Comment_398969","dateInserted":"2023-10-06T19:01:55+00:00","insertUserID":159342,"insertUser":{"userID":159342,"name":"Beth Fantozzi 1","url":"https:\/\/community.smartsheet.com\/profile\/Beth%20Fantozzi%201","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-06T19:37:27+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\/8PZPFEHHKZ5S\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-06T19:01:13+00:00","dateAnswered":"2023-10-06T18:49:07+00:00","acceptedAnswers":[{"commentID":398967,"body":"
@Beth Fantozzi 1<\/a> <\/p> I got you - use this:<\/p> =IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), IF(AND(NOT(ISNUMBER([Quote Amount]@row)), NOT(ISNUMBER([Actual Amount]@row))), 0, IF(ISNUMBER([Actual Amount]@row), [Budgeted Amount]@row - [Actual Amount]@row, [Budgeted Amount]@row - [Quote Amount]@row)))<\/p><\/div><\/div> It ends up looking like this:<\/p> <\/p> _____________________________________________________________________________________________<\/p> 👨🏼💻 Dan Palenchar | <\/strong>School of Sheets Solutions Consulting<\/strong><\/a> | Smartsheet Aligned Gold Partner<\/strong><\/p> If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and\/or ❤️Awesome).<\/em><\/p> 🆘 Smartsheet Consulting Inquiries: schoolofsheets.com\/workwithus<\/a><\/p> ▶️ Smartsheet Tutorial Videos: schoolofsheets.com\/youtube<\/a><\/p>![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/5U7E27I89YBF\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
<\/p>
<\/p>
<\/p>
<\/p>
<\/p>
<\/p>
<\/p>
<\/p>
<\/p>
