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:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
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?
☑️ 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([email protected]))
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([email protected] = "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([email protected] = "Total", SUM(CHILDREN([email protected]), ""))
Alternatively, you can check for parents using the column formula in a column called Level with the formula: =COUNT(ANCESTORS([email protected]))
Then to sum the children: =IF([email protected] = 0, SUM(CHILDREN([email protected]), ""))
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:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

@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([email protected] = "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:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.


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:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

@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:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
Help Article Resources
Categories
Check out the Formula Handbook template!