Multiply each child row by its own multiplier before finding the sum of all products.

Haley Wampner

I need to multiply each child row subtotal by its own qty value before finding the sum of the results of all. I currently have =SUM(CHILDREN(Subtotal@row*QTY@row)) and I am getting the #UNPARSEABLE error. Subtotals at each row should maintain the cost for a singular qty while the grand total incorporates the quantities of each item. Please help.

prime_nathaniel

@Haley Wampner

=SUM(CHILDREN(qty@row)) * SUM(CHILDREN(SubTotal@row))

That said, you may want this in a total column, you also may want to use helper columns and if then to set different formulas for parent rows vs child rows.

Haley Wampner

@prime_nathaniel Your formula multiplies a collective subtotal by a collective quantity rather than each individual subtotal by its applicable quantity before providing a sum of the products.

My current work around is the formula below, but ideally, I would like to prevent referencing specific cells to allow for additional categories/subtotals/child rows without rewriting the formula every time.

=(Subtotal2 * QTY2) + (Subtotal384 * QTY384) + (Subtotal394 * QTY394) + (Subtotal396 * QTY396)

prime_nathaniel

@Haley Wampner , just realized, is thee a reason why there isnt a price or cost column? Didn't hit me till now that you are using subtotal for price or cost, and for subtotal, and for total. Or am I reading that incorrectly?

So if Subtotal is really price or cost, couldn't you just add a column called "line total" or something. Then use a column formula on "line total" for QTY * SubTotal. You can even use help column or ancestor() to ONLY do this for your sub blue or yellow row hierarchy. Then you could have you else (in the if) be used for the grand total blue line to just be a sum children(line totals).

Haley Wampner

@prime_nathaniel There are several nesting parent/child hierarchies, each containing actual list items, each with their own quantities and costs. Subtotal column serves as a line item total (=(Labor Cost@row +Material COst @row)*QTY@row)(white rows) as well as a divisional subtotal (using =sum(children()) (Green rows), and an overall subtotal (also using =sum(children())) (yellow rows). It is at this point that in need to apply an overall quantity to each yellow row to get my grand total (Blue).

DKazatsky2

Hi @Haley Wampner,

Are you able to add columns? If so, I believe I have a solution that will suit your needs using 2 "helper" columns, which can be hidden.

First, add column to track the hierarchy level of each row with the using the following column formula. I have titled this column "Level (Helper).

=COUNT(ANCESTORS())

Next, add a column that will house the extended price for only the rows you have designated in yellow, which is level 1, using the following formula. I titled this column "Price Extended (Helper)"

=IF([Level (Helper)]@row = 1, Subtotal@row * QTY@row)

Lastly, in the "Subtotal" column, just in the first row, use the following formula.

=SUM([Price Extended (Helper)]:[Price Extended (Helper)])

Hope this helps,

Dave

prime_nathaniel

@Haley Wampner solution should still work fine with the tools stated. Are you comfortable using the ancestor or children functions? if you can write a formula for each row you can nest if()'s together to do it automatically as a column formula.

Haley Wampner

Seems odd to have to track the hierarchy levels in a separate column when the hierarchy was already created and identified in the primary column, but I will give this a shot and see if it works.