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!