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 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!
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 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: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
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!