How would I skip parent rows in a column formula?

Sandra Bauman · 02/24/21

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!

Paul Newcome · 02/24/21

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

Paul Newcome · 02/24/21

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

Sandra Bauman · 02/24/21

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 self-evident) that made

Paul Newcome · 02/25/21

Happy to help. 👍️

TeeM · 03/08/22

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

jchalig · 04/18/23

I agree.

TeeM

Mar 8, 2022

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

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

Paul Newcome · 04/18/23

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)

meirney · 08/03/23

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!

Paul Newcome · 08/03/23

@meirney You don't need that part. It would just be

=IF(COUNT(CHILDREN()) = 0, child_row_formula, parent_row_formula)

meirney · 08/04/23

Paul Newcome

Aug 3, 2023

@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.

Paul Newcome · 08/04/23

Happy to help. 👍️

Formulas and Functions

Formulas and Functions

How would I skip parent rows in a column formula?

Best Answer

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Finding if a date falls in a range in a referenced sheet

CountIF contains name from a contact list help needed

Formula for averaging days