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!
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 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
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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!