Sum of Children While Maintaining Column Formula

Yvette Moreland

It would be incredibly helpful to allow for the sum of the children on a parent row, but maintain the column formula.

Genevieve P.

Hi @Yvette Moreland

We may be able to build this into your formula! Can you clarify what formula you're using in the child rows?

Yvette Moreland

I have columns for travel expenses and some of the columns are formulas. The rows are the names and dates of the travelers. One example: HOTEL NIGHTLY RATE and NUMBER OF NIGHTS are columns and the TOTAL HOTEL COSTS column is a formula multiplying those two. I need totals by column, so I have the travelers as child rows so the parent row will give me the totals. The challenge is when someone inserts a new row, (rather than copying a blank row) they lose the formulas. I would like to have the column formulas, but keep the parent row as the totals. I haven't found a workaround. This is a template used to make hundreds of versions for our different locations, and many users are beginning Smartsheet users. I am trying to make it more user friendly.

Genevieve P.

Hi @Yvette Moreland

Thank you for outlining your sheet! I definitely believe this is possible to do for your scenario.

We can first check how many children a current row has. If the current row has 0 children, it is a child row itself and you do your multiplication formula. However if there is at least 1 child row beneath the current row, you want the formula to SUM the child values.

Try something like this:

=IF(COUNT(CHILDREN([Primary Column]@row)) = 0, ([HOTEL NIGHTLY RATE]@row * [NUMBER OF NIGHTS]@row), SUM(CHILDREN()))

Note that you'll need to change out the [Primary Column] name to your column name. Does that make sense?

Cheers,

Genevieve