Need to set up column formula with =SUM(CHILDREN()) in one field

Marina · 07/22/21

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.

Andrée Starå · 07/23/21

@Marina

You have to add it to a new column, a helper column.

Bassam Khalil · 07/22/21

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?

James Keuning · 07/22/21

Like this?

=SUM(CHILDREN(cost@row))

You can do some formula modification to only include the result in the parent rows.

Marina · 07/23/21

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

Marina · 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

James Keuning · 07/23/21

https://community.smartsheet.com/discussion/comment/298857#Comment_298857

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?

Marina · 07/23/21

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)

Andrée Starå · 07/23/21

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!

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!

Marina · 07/23/21

@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?

Andrée Starå · 07/23/21

@Marina

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?

Marina · 07/23/21

@Andrée Starå

I used this formula but getting an error.

Andrée Starå · 07/23/21

@Marina

You have to add it to a new column, a helper column.

Marina · 07/23/21

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

Andrée Starå · 07/23/21

@Marina

Happy to help!

Formulas and Functions

Formulas and Functions

Need to set up column formula with =SUM(CHILDREN()) in one field

Best Answer

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Formula to return a date when value in one sheet matches value in another.

Formula with multiple checkboxes and each has an associated comment

COUNTIF with CONTAINS Error – Looking for a Solution