Sum of children if it has no descendants

Nathaniel

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?

Paul Newcome

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

MichaelTCA

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!