Need to set up column formula with =SUM(CHILDREN()) in one field
I have parent rows set up where it's always have "Total" in one of the fields
How do I set up column formula to calculate CHILDREN rows each time under PARENT row? Currently it's only working with cell formula as =SUM(CHILDREN()) but I would like this field automatically updated every time I add parent and children rows.
Best Answer

You have to add it to a new column, a helper column.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers

Hi @Marina
Hope you are fine, did you mean that you need to have column format formula that check if the level is parent then SUM the children of that parent?
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Like this?
=SUM(CHILDREN(cost@row))
You can do some formula modification to only include the result in the parent rows.

@Bassam Khalil Yes, I would need to have column formula if the level is parent then SUM the children of that parents. I don't want to have another column created as @James Keuning mentioned. We specify the parent row by including 'Total' in one of the fields.

I tried IF(TOTAL@row = "Total", SUM(CHILDREN())) but it only worked as cell formula, I would need to use as column formula

As you can see in my screen shot, I am using a column formula. What error are you getting when you try the formula?
You can test for parent by using this formula, sorry I did not catch that the first time:
IF(Total@row = "Total", SUM(CHILDREN(cost@row), ""))
Alternatively, you can check for parents using the column formula in a column called Level with the formula: =COUNT(ANCESTORS(ID@row))
Then to sum the children: =IF(Level@row = 0, SUM(CHILDREN(cost@row), ""))
Those are both column formulas.
Can you tell us more about why you do not want to create another column, and how your sheet is currently laid out?

I want to use SUM(CHILDREN) function at the same column, currently I can only use when manually add new PARENT row by entering the formula, I want to use column formula so it's SUM automatically each time you add new PARENT and CHILDREN row. We have multiple column where I would need to use this formula and don't want to create additional columns just only to SUM PARENT. Here is an example. I want to use column formula under Sum 1 and Sum 2 to calculate automatically for each "TOTAL" task. Currently I have to enter manually =SUM(CHILDREN)

Hi @Marina
I hope you're well and safe!
Unfortunately, it's not possible because you can't combine formula and manual values as a column formula (you would have to use a socalled helper column), but it's an excellent idea!
Please submit an Enhancement Request when you have a moment.
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

@Andrée Starå how would I use socalled helper column here? Would TOTAL column with "TOTAL" in row not help me to create column formula?

Here's one way, but we could also add a check if it's a parent and use that instead of the TOTAL Column.
=IF(TOTAL@row = "Total", SUM(CHILDREN()), [Sum 1]@row)
Make sense?
Would that help?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.


You have to add it to a new column, a helper column.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

@Andrée Starå thank you for your help. Since I don't want to use different column just for SUM(CHILDREN) value, I will continue to copy this formula manually when I add new rows.

Happy to help!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 351 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 443 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!