Sum all children rows under parent
I have my project worksheets organized by year, quarter, month, project, etc. Is there a simple formula to sum all children rows one indent in? For example, all months sum up to the quarters which sum up to the year? I only want them to sum at one indent in from the immediate parent row.
Best Answers
-
Hi @HH_BE1894
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
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
-
Hi @HH_BE1894
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
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thanks, that helps! How do I write the formula then to sum all the level 2s within that level 1 only?
-
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.
-
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!
-
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! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!