Sum all children rows under parent

HH_BE1894

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.

Andrée Starå

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!

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.

Andrée Starå

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!

HH_BE1894

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))

HH_BE1894

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.

HH_BE1894

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å

@HH_BE1894

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!

Paul Newcome

Happy to help. 👍️