Sum of all descendants

ricki

Hi

 

I am trying to add a formula to get the sum of all descendants. I have used =SUM(CHILDREN()) but that only adds in direct children. I need to also be able to add in the grandchildren, greatgrandchildren, etc.

 

Any way to do this?

 

thanks

Rob Hagan

Hi Ricki,

 

One solution is to have the item that you wish to sum in (say) Column3 and then put this formula in [Column4]1: =IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), [Column3]1) and copy it down to the extent of the used rows in the sheet. This assumes that there is no data in Column3 for any parent and that only child rows have data.

 

If you wish that both parent and child rows have data in Column3 to sum, then use this in [Column4]1 and copy through as before: =[Column3]1 + IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), 0)

 

Cheers,

Rob.

ricki

in my case the parent could have data independant of the children.

 

I tried your formula but it tells me unparsable

 

my column name is "Estimation of Effort" and it is the 6th column. What should go in the bracket?

 

it also tells me unparsable if all i try is =SUM(CHILDREN()), [Column6]1)

Rob Hagan

Ricki,

The column is not relevant as such, as SmartSheet refers to things as a column name plus a row number. If you put the following formula in row 1 of a newly created column and copy it down beside your data in the Estimation of Effort column, then you should be fine. This expression says "return the sum of two values, the one in the Estimation of Effort cell on this row and the sum of the children of that same Estimation of Effort cell on this row if there are any children":

=[Estimation of Effort]1 + IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN([Estimation of Effort]1)), 0)

Hope that this helps...

Rob

JamesR

Look at the help here:

https://help.smartsheet.com/articles/775363-using-formulas

and click on: try the Smartsheet Formula Examples template.

 

This will create a copy in your Smartsheet area.  Its very useful.

 

Russell888

I know this is probably a late reply but here is the perfect/error proof way to sum all descendants or children. 

Put the following formula in every parent row in the column where you have your number/order values.

=SUM(CHILDREN())

If you do not wish to see each total in each of the rows where you put this formula you can hide it by changing the text colour to the same colour as the cell background. For example, white text on a white background.

In the cell where you want your total for all descendants/children to appear, reference the top parent cell where you put the above formula, for example:

=[Order QTY]1

If this is not clear I can add a screenshot of what I mean.