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 markup % (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!
Answers

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 recreated 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
Regards
Sean

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
Categories
Check out the Formula Handbook template!