How to sum children rows and exclude parent/grandparent?

Ryanne Kwan

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:

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

Please help.

Linda Manduchova

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

Linda Manduchova

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

Ryanne Kwan

That worked brilliantly. Thank you so much!