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!

Find more posts tagged with

Formulas

functionality

Accepted answers

All comments

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

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.