How to sum children rows and exclude parent/grandparent?
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.
Best 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
Answers
-
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
-
That worked brilliantly. Thank you so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!