Sum of Children While Maintaining Column Formula
It would be incredibly helpful to allow for the sum of the children on a parent row, but maintain the column formula.
Best Answer

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
Answers

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

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.

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

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.

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()))

Of course! That is it. Thank you so much for your help.
Help Article Resources
Categories
Check out the Formula Handbook template!