# 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.

• ✭✭✭✭✭✭

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?

PMP Certified

[email protected]

www.mobilproject.it

• ✭✭✭✭✭

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.

• edited 07/23/21

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 so-called helper column), but it's an excellent idea!

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 for help with Smartsheet, integrations, general workflow advice, or anything else.

• @Andrée Starå how would I use so-called 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:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• I used this formula but getting an error.

• ✭✭✭✭✭✭

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 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:[email protected] | 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!