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!
