Exclude a child row from parent row with a formula that sums its children

leapm

I would like to add a row with formulas under a parent row that calculates the sum of its children. So i need the parent row to skip one of its children that has formulas instead of the content being summed.

is this possible?

snipet.png

In the screenshot you can see when I try to add another row of formulas, i get a circular reference.

akrenek

Hi @leapm --Have you tried the following formula =SUM(CHILDREN())-[Cell you don't want added]? I included a screen shot example below. I want my parent row to add the amount financed for the first three children, but exclude the last. The formula correctly calculates with $50K.

leapm

I still get a circular reference.

snipet2.png

Parent formula =SUMIF(CHILDREN(), "Active", CHILDREN([Monthly Allocation]@row)) - January37

Row below =SUM([Monthly Allocation]36 - January36)

Trying to have two rows of formulas. Adding all children in the parent row that are below these two formulas.

Any help is appreciated!Thank you!

Genevieve P.

Hi @leapm

Can you clarify what you're doing in the second formula, the row below? It looks like you're referencing the cell above, which is referencing that cell below, hence the circular reference.

You'll need to have that specific formula in a different place in your sheet, it won't be able to be a "child row" for your previous formula.

Cheers,

Genevieve