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

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

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

Yvette Moreland

Hi Genevieve,

Thank you for the response. Unfortunately, I am not getting it to work.

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

calculates the child rows, but when I change it a column formula, it does not sum the parent row.

I tried other columns, and I have the same issue when I made the formula a column formula. (FYI, my Primary Column is titled ".")

# of nights column:

=IF(COUNT(CHILDREN([.]@row)) = 0, ([Departure Date]@row - [Arrival Date]@row), SUM(CHILDREN()))

This returns blank on parent row

Mileage Costs (2024 rate $0.67) column:

=IF(COUNT(CHILDREN([.]@row)) = 0, ([If Driving - Miles (from home location)]@row * 0.67) * 2, SUM(CHILDREN()))

This returns $0 on the parent row.

Genevieve P.

Hi @Yvette Moreland

The reason you're seeing 0 in the top parent row is because the Primary column you're referencing has "no children" because the cells below it are blank.

Try referencing a column that will always have text in it, perhaps the Name column?

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

Yvette Moreland

Of course! That is it. Thank you so much for your help.