How would I skip parent rows in a column formula?
I want to use a column formula to track time spent on various tasks. Each task row would fall under various Parent categories. I have a start time and end time column, and time would only be entered in the Task row, not the Parent row.
I found an excellent (really long) formula to figure out the total time in a new column in an old Smartsheet community string. However, I want it to apply to the whole column  except for the Parent rows. What would I add to the formula in order to exclude the Parent rows?
Thanks, in advance, for any ideas!
Best Answer

To exclude parent rows, wrap your entire formula in an IF statement.
=IF(COUNT(CHILDREN()) = 0, original_formula)
As for the time formula... HERE is a link to a thread that has quite a few different time based solutions. At this time (24 Feb 2021) it has 4 pages, so you may have to look around a little bit, but there may be something in there to help you with a shorter formula.
If you are using the formula I think you are, it is a monster, and can actually exceed the 4,000 characters per cell limit depending on your column name(s)!
Answers

To exclude parent rows, wrap your entire formula in an IF statement.
=IF(COUNT(CHILDREN()) = 0, original_formula)
As for the time formula... HERE is a link to a thread that has quite a few different time based solutions. At this time (24 Feb 2021) it has 4 pages, so you may have to look around a little bit, but there may be something in there to help you with a shorter formula.
If you are using the formula I think you are, it is a monster, and can actually exceed the 4,000 characters per cell limit depending on your column name(s)!

Thank you, Paul. I had actually tried that 'prefix' to the other formula, and it didn't work for me. But when I copied and pasted it (twice) from your answer, it did. So I must have had an extra something (comma, parentheses, space, who knows because it wasn't selfevident) that made

I have the same scenario but want the Parent row to show totals of that column

I agree.
It would be great to the parent row still function to sum the children, while the children run the column formula.

It is possible to have separate calculations run on parent and child rows. You would include the parent row formula in the 3rd portion of the IF statement from above.
=IF(COUNT(CHILDREN()) = 0, original_formula, parent_formula)

i'm trying to do a subtotal of the children rows (but making sure that these children rows still have the column formula). basically, all rows should be a certain formula except the parent row.
in other words:
parent = subtotal of children
children = formula
it works until i added the IF(COUNT(PARENT()) ...
it's saying "column formula syntax isn't quite right".
thanks in advance for any response!

@meirney You don't need that part. It would just be
=IF(COUNT(CHILDREN()) = 0, child_row_formula, parent_row_formula)

it works now! thanks a lot, Paul. very helpful.
Help Article Resources
Categories
Check out the Formula Handbook template!