Column formula to sum children
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
SYLVIA KAY  DIRECTOR PROJECT MANAGEMENT
PLATFORM ENGINEERING & TECHNOLOGY TEAM
AMERICAN EXPRESS GLOBAL BUSINESS TRAVEL
M: 404664 1025  SYLVIA.KAY@AMEXGBT.COM
follow us on twitter: @AmexGBT
follow us on instagram: @AmexGBT
Best Answer

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
Answers

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

Hi Kelly, thank you so very much, that formula works. Still lots to learn for me in the formula category :)
SYLVIA KAY  DIRECTOR PROJECT MANAGEMENT
PLATFORM ENGINEERING & TECHNOLOGY TEAM
AMERICAN EXPRESS GLOBAL BUSINESS TRAVEL
M: 404664 1025  SYLVIA.KAY@AMEXGBT.COM
follow us on twitter: @AmexGBT
follow us on instagram: @AmexGBT

@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?

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

@Kelly Moore Thanks Kelly!
Help Article Resources
Categories
Check out the Formula Handbook template!