Sum all children rows under parent

Options
HH_BE1894
HH_BE1894 ✭✭✭✭
edited 07/17/23 in Formulas and Functions

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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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å
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

  • HH_BE1894
    HH_BE1894 ✭✭✭✭
    Options

    Thanks, that helps! How do I write the formula then to sum all the level 2s within that level 1 only?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    HH_BE1894 ✭✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    HH_BE1894 ✭✭✭✭
    Options

    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å
    Andrée Starå ✭✭✭✭✭✭
    Options

    @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!

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!