Sum children with one child omitted


I am struggling with a formula that I thought would be easy. I want to sum all children of Total column while omitting one child (Taxes & Freight). The purpose of this is to then multiply it all by Tax and Freight mark-up % (Qty2051) .

Obviously I can manually add the various children by referencing the row numbers, but I want to avoid that so that the formula remains functional even if someone adds or deletes a child of row 1837.

As per the image above, I'd like to sum the children in G&A while omitting Taxes&Freight. Then multiply that value by 1%. This calculation should be located in cell Total2051.

Everything I tried results in a #blocked or #circular reference.

Thank you in advance!


  • Sean Morgan

    Hello @Vitaly R. ,

    I was able to create an example that should return the result you are looking for. Please see this below

    • As you can see, i've re-created your sheet. The totals for each parent row are calculated using =SUM(CHILDREN(Total2:Total11)), thus returning $136,240
    • I then got the total for each children row under each header (Operation Vehicles, OSE Supplies etc) using =SUM(CHILDREN())
    • Then, in the Total Column cell on the same row as "Taxes & Freight", this was calculated using =Total1 * Qty14 which then returns the result of $1,362.40. Total1 is just the cell on the first row contains the total of $136,240
    • To times by 1%, I just entered an asterisk after the parent row total SUM's reference and click the cell that contains the percent.

    Here are the articles I used to create this example:

    Let me know if you have any questions



  • Vitaly R.
    Vitaly R. ✭✭✭

    Hello Sean,

    Thank you for the reply. Unfortunately, I need Taxes & Freight to be a child of G&A. In your example it is not, therefore no Circular Reference is created. I have found a workaround for my problem, but it is not ideal:

    In my example above, it all works as I want it to but I am unable to show the Taxes & Freight amount in row 2045 without creating a Circular Reference.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!