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!

Find more posts tagged with

Accepted answers

Paul Newcome

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

All comments

Paul Newcome

To exclude parent rows, wrap your entire formula in an IF statement.

=IF(COUNT(CHILDREN()) = 0, original_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

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

Happy to help. 👍️

TeeM

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

jchalig

I agree.

https://community.smartsheet.com/discussion/comment/321846#Comment_321846

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

Paul Newcome

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

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

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

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

meirney

https://community.smartsheet.com/discussion/comment/388757#Comment_388757

it works now! thanks a lot, Paul. very helpful.

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.