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

edited 05/04/23 in Formulas and Functions

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?

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


  • akrenek
    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.

    Aleya Krenek

    Education Service Center Region 13

    Grants and Contracts Project Coordinator

  • I still get a circular reference.

    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.
    Genevieve P. Employee Admin

    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.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!