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
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
Comments
-
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.
-
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)
-
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
-
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.
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives