Column formula to sum children

Sylvia Kay

I am struggling with my formula.

I have a helper column that identified Parents (1) and Children (empty)

I have a column that contains a value for all children,

I like to create a column formula that always provides a total of all children values at the parents row (yellow) when the parent has children.

My formula has an error:

=IFERROR(IF([Helper Column]@row = 0, SUM(CHILDREN([Weight / Story Points]@row), "")))

Thank you for the guidance

Kelly Moore

Hey @Sylvia Kay

A few corrections are needed in the formula above. The criteria in the IF needs to be adjusted. The Child rows (=0) do not contain Children, only Parent rows do. Swap the IF criteria to a 1. Also the syntax in the formula isn't what is expected. A rule of thumb is to create a working formula first before trying to add an IFERROR. The IFERROR will wrap the formula so one only needs to add the IFERROR part to the end of the existing formula.

=IFERROR(IF([Helper Column]@row = 1, SUM(CHILDREN([Weight / Story Points]@row))), "")

Does this work for you?

Kelly

Kelly Moore

Hey @Sylvia Kay

A few corrections are needed in the formula above. The criteria in the IF needs to be adjusted. The Child rows (=0) do not contain Children, only Parent rows do. Swap the IF criteria to a 1. Also the syntax in the formula isn't what is expected. A rule of thumb is to create a working formula first before trying to add an IFERROR. The IFERROR will wrap the formula so one only needs to add the IFERROR part to the end of the existing formula.

=IFERROR(IF([Helper Column]@row = 1, SUM(CHILDREN([Weight / Story Points]@row))), "")

Does this work for you?

Kelly

Sylvia Kay

Hi Kelly, thank you so very much, that formula works. Still lots to learn for me in the formula category :-)

Mitch Cohen

@Kelly Moore Kelly, I am very green with Smartsheet, so forgive me for the dumb question, but in Sylvia's example above, why would she not use SUM(CHILDREN(() this have the sum of the children rows appear in the yellow highlighted area?

Kelly Moore

Hey @Mitch Cohen

Good question. In the example above, the values to be summed were not in the same column, as is assumed when one uses only Children() with the empty parentheses. By including the column name within the parentheses, the formula was directed to which column to sum. The IF statement was there to only produce a value when the IF condition was met.

Does this help?

Kelly

Mitch Cohen

@Kelly Moore Thanks Kelly!