Sum all children rows under parent
Best Answers
-
Andrée Starå ✭✭✭✭✭✭
I hope you're well and safe!
You can add a so-called helper column that indicates a level and then use that in the calculation.
Try something like this for the helper column. (change the column names as needed)
I usually add a so-called helper column called Level with the formula below, and then you can use it to identify parents and use it for conditional formatting and more.
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS()) + 1)
Would that work/help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
✅我的帖子(s) help or answer your question or solve your problem? Please support the Community bymarking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
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 about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Paul Newcome ✭✭✭✭✭✭
Right. You would have to put
=SUM(CHILDREN())
into each parent row manually, but that is no different from any other formula that you would want applied to only parent rows while keeping child rows as editable.
The difference is no need for a helper column. What you are describing is exactly how the CHILDREN() function operates. Putting that function on Level 1 means it will only pull in rows that are on its own Level 2.
Unless I am misunderstanding your structure, here is an example where I put
=SUM(CHILDREN())
on each parent row and left the child rows as manual entry. Each month is a sum of its projects. Each quarter is a sum of its months, and the year is a sum of its quarters.
Answers
-
Andrée Starå ✭✭✭✭✭✭
I hope you're well and safe!
You can add a so-called helper column that indicates a level and then use that in the calculation.
Try something like this for the helper column. (change the column names as needed)
I usually add a so-called helper column called Level with the formula below, and then you can use it to identify parents and use it for conditional formatting and more.
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS()) + 1)
Would that work/help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
✅我的帖子(s) help or answer your question or solve your problem? Please support the Community bymarking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
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 about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Thanks, that helps! How do I write the formula then to sum all the level 2s within that level 1 only?
-
Paul Newcome ✭✭✭✭✭✭
You should be able to just use
=SUM(CHILDREN())
on all parent rows.
If you want the calculations in a separate column:
=SUM(CHILDREN([Column Name]@row))
-
I believe I'll have to keep my Cost column at the cell formula level instead of a column formula. I added a Level column as directed. Within the Cost column, I'm trying to enter costs per task but have the parent rows summarize the children underneath them. So parent rows @ level 1 will sum parent rows @ level 2. Parent rows @ level 2 will sum parent rows @ level 3. If Level 3 rows have children, I'll continue the formula and so forth. At the lowest level, I want to be able to manually log the cost of that task. Does that make sense?
The goal is to look at a project plan and see total spend by year, quarter and project. All the costs of the tasks within a project roll up, costs of projects roll up to quarter, quarters roll up to year.
-
Paul Newcome ✭✭✭✭✭✭
Right. You would have to put
=SUM(CHILDREN())
into each parent row manually, but that is no different from any other formula that you would want applied to only parent rows while keeping child rows as editable.
The difference is no need for a helper column. What you are describing is exactly how the CHILDREN() function operates. Putting that function on Level 1 means it will only pull in rows that are on its own Level 2.
Unless I am misunderstanding your structure, here is an example where I put
=SUM(CHILDREN())
on each parent row and left the child rows as manual entry. Each month is a sum of its projects. Each quarter is a sum of its months, and the year is a sum of its quarters.
-
Thanks! I'll do that then. I was trying to turn it into a column formula for ease and scaling, but it wouldn't work because of the rows with no children. Thanks for your help!
-
Andrée Starå ✭✭✭✭✭✭
Excellent!
Happy to help!
This might be helpful if you want to count specific levels.
First, add the helper column like the one below to get the level.
Try something like this in a so-called helper column called Level.
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS()) + 1)
Secondly, try something like this in the count column.
=COUNTIFS(Level:Level, 1)
✅Remember!我的帖子(s) help or answer your question or solve your problem? Please support the Community bymarking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
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 about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Paul Newcome ✭✭✭✭✭✭
Help Article Resources
Categories
Try this:<\/p>
=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/107653/\")<\/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":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":44,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+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-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"