Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Sum of all descendants

Options
ricki
ricki ✭✭✭✭✭✭
edited 12/09/19 in Archived 2016 Posts

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

Tags:

Comments

  • Rob Hagan
    Rob Hagan ✭✭✭
    Options

    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
    ricki ✭✭✭✭✭✭
    Options

    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
    Rob Hagan ✭✭✭
    Options

    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
    JamesR ✭✭✭✭✭✭
    Options

    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
    Options

    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.

This discussion has been closed.