Sum of children if it has no descendants
I am attempting to create a single cell formula to capture the total value within a budget sheet, however =SUM(DESCENDANTS()) is capturing data multiple times and doubling costs due to children rows being routinely being summed (=SUM(CHILDREN)) by staff for reconciliation purposes.
The sheet layout has a parent row with multiple children and differing numbers and levels of descendants (e.g. child 1 has no descendants, child 2 has 3 grandchildren, etc). Is there a way to sum all descendants of a cell, only if the cell has no descendants of its own?
Answers

Try just using the =SUM(CHILDREN()) on each of the parent rows.

Hello Nathanial,
Hierarchies are built up in this sort of fashion. This means we are always evaluating a range of cells, AND most importantly, that range is always static.
You could use a helper column to give a value to the row and use a conditional statement. We are evaluating row by row in "Column6".
This function will tell you how many descendants there are. We will use another SUMIF statement to give us the sum of ONLY values that don't have children. Again we are evaluating row by row in "Column5"
14 is the SUM of all values within the hierarchy column that have no CHILDREN. AND since the top of the hierarchy has no ASCENDANTS, the initial value is never included (not being a descendant itself).
You would need all 3 columns of these columns. Hierarchy represents the initial data to be evaluated, the Count column represents the loop as each cell is evaluated, and then Combine Functions column is the return value.
The combine functions column only needs to be in 1 cell. The ranges being evaluated are already specified, so it doesn't need to evaluate row by row like the helper "loop". Then you can hide the helper columns.
Hopefully this helps!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 64 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!