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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

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
 Smartsheet Customer Resources
 62.9K Get Help
 378 Global Discussions
 208 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 289 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!