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

07/22/21
Accepted

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

Answers

  • Bassam KhalilBassam Khalil ✭✭✭✭✭

    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

    ☑️ 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.

  • MarinaMarina
    edited 07/23/21

    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)



  • Andrée StaråAndrée Starå ✭✭✭✭✭

    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 PARTNER & CONSULTANT / EXPERT

    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 so-called helper column here? Would TOTAL column with "TOTAL" in row not help me to create column formula?

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @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([email protected] = "Total", SUM(CHILDREN()), [Sum 1]@row)
    

    Make sense?

    Would that help?

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    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å

    I used this formula but getting an error.



  • Andrée StaråAndrée Starå ✭✭✭✭✭
    Accepted Answer

    @Marina

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

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    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.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @Marina

    Happy to help!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    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.

Sign In or Register to comment.