Sum children with one child omitted

Vitaly R.

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:

• https://help.smartsheet.com/function/sum
• https://help.smartsheet.com/function/children

Let me know if you have any questions

Regards

Sean

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.