Parent child sum formula help
I need some assistance with a formula. The requirements are:
if a row is a parent, then sum the values of its child rows in column A
If the row is a child, ""
if the row is not a parent AND is not a child, then return the value in column A for that row.
I have tried numerous permutations, some of which get close, but none that work.
Answers
-
I believe you will need a helper column, called "children" or whatever you want.
in that column you will have a column formula, =count(children()), and you will use those results to determine if you are in a child row or a parent row
-
I think this is does what you are looking for where cost represents your column A:
=IF(COUNT(CHILDREN(cost@row)) > 0, SUM(CHILDREN(cost@row)), IF(COUNT(PARENT(cost@row)) > 0, "", cost@row))
Your scenario doesn't mention what to do with the value in column A on the parent row when there are children. Is it to be included in the SUM as well?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!