Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    How to sum children rows and exclude parent/grandparent?

    ✭✭
    in Formulas and Functions

    I am trying to pull a report from my sheet to sum the children rows, and exclude the parent and grandparent.

    I am tracking budget expenses and have the totals summed on multiple parent rows (light blue), which rolls up to the main grandparent row (dark blue). I'd like to summarize the children based on status of the task to track expenses: completed, in progress, not started.

    Here's my sheet:

    image.png


    Here's what I'm trying to summarize. I've tried multiple formulas and they aren't working.

    image.png


    Please help.

    · Share on FacebookShare on Twitter

    Best Answer

    • ✭✭✭✭
      Answer ✓

      Hi @Ryanne Kwan -


      I would recommend to create a helper column and label it "Ancestors" and then apply the following formula:

      =SUMIFS(budget:budget, status:status, "Completed", Ancestors:Ancestors, 2)

      For the "Ancestors" helper column, I would apply the following formula:

      =COUNT(ANCESTORS())

      So, first you will implement the helper column and then you can sum your budget or actuals according to your status and the number of ancestors. It depends how many ancestors your most indented rows have. In my example, my most indented rows had 2 ancestors and I summed only those.


      Hope this helps!


      Linda

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭
      Answer ✓

      Hi @Ryanne Kwan -


      I would recommend to create a helper column and label it "Ancestors" and then apply the following formula:

      =SUMIFS(budget:budget, status:status, "Completed", Ancestors:Ancestors, 2)

      For the "Ancestors" helper column, I would apply the following formula:

      =COUNT(ANCESTORS())

      So, first you will implement the helper column and then you can sum your budget or actuals according to your status and the number of ancestors. It depends how many ancestors your most indented rows have. In my example, my most indented rows had 2 ancestors and I summed only those.


      Hope this helps!


      Linda

      · Share on FacebookShare on Twitter
    • ✭✭

      That worked brilliantly. Thank you so much!

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions