Totals and Sub Totals
Sorry if this is an obvious question but I am new to Smartsheet and I am trying to get my head around Totals and Sub Totals.
In our Project Plans we have 3 columns relating to Effort:
 Planned Effort
 Time Spent
 Effort Remaining
What I need is to be able to get a Total for each column in say Row 1 and Sub Totals for each Parent Task. Whilst I can do this by placing the following formula manually into every Parent row is there an easier way to do this so if the hierarchy of tasks are changed the the formulas don't have to be recopied?
 Sum(children())
Whilst I have tried using the following formula in Row 1, I get an '#unparseable' error
 =sum(PARENT([Planned Effort (Hours)]:[Planned Effort (Hours)])
In the attached Image I would like to get:
 Totals of the children values in the coloured rows
 Grand total of each column in Row 1, excluding the Sub Totals
Questions:
 Can these two things be done?
 If so, can they be automated so that if new Parent tasks are added the Children formula above, does not have to be added to each Parent row?
Thanks for your help and understanding!
Best Answer

Hi @Nigel Michell,
should be easy by using the proper indentation and thus a hierarchy the SUM(CHILDREN()) formula understands.
You want to see 110 I 106 I 0 in the first row of your example and 56 I 57 I 0 in the second row, correct?
Make row 1 unindented, indent row 2 once, everything before the green row twice, then the green row needs to be indented only once etc...
Place the formula =SUM(CHILDREN()) in every colored cell and every cell of row 1 and that's it.
As long, as you keep the children elements at the right level, the formula will always work ok and the formula in row 1 only counts the collared values.
Hope this helps
Stefan
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
Answers

Hi @Nigel Michell,
should be easy by using the proper indentation and thus a hierarchy the SUM(CHILDREN()) formula understands.
You want to see 110 I 106 I 0 in the first row of your example and 56 I 57 I 0 in the second row, correct?
Make row 1 unindented, indent row 2 once, everything before the green row twice, then the green row needs to be indented only once etc...
Place the formula =SUM(CHILDREN()) in every colored cell and every cell of row 1 and that's it.
As long, as you keep the children elements at the right level, the formula will always work ok and the formula in row 1 only counts the collared values.
Hope this helps
Stefan
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.

Hi @Stefan
Thanks for your response  yes that is exactly what I want.
I will try your solution and then update this post with my findings.
Thanks again
Nigel


Pleasure
greetings
stefan
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.

@Stefan  Thank you very much for this great idea. It really works well.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 63 Global Discussions
 46 Industry Talk
 387 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!