Sum(Children())

Another basic question - I have a column that uses the value in the 'Remaining Effort' to calculate the status of that task.

At the Parent Level I am using Sum(Children()) to sum the Remaining Effort for the Child tasks. This works fine as long as there is a value in this field - if the Task has not started and 'Remaining Effort' is blank then the Sum(Children()) formula will return '0', which is then used to indicate that the Parent Task is complete, which is obviously not correct.

Is it possible to modify the Sum(Children()) formula so that it only sums Child Tasks if their values are not blank? If all the values are blank then it should return "".

Thanks for sharing your expertise!

Nigel

Find more posts tagged with

Accepted answers

David Joyeuse

Hi @Nigel Michell

Sorry for the delay, I've been a little bit too busy to come back to you.

Row 4B, to me, is nothing more than an helper column you hide. I like to have this row which can help greatly in card view, if you use it . It's also great to make it easier to differentiate parents from childrens on the grid with conditional formatting as you can apply a specific line color, font, character hieght, and so on based on this column. It really helps having a cleaner sheet to work with.

Now regarding the calculated status column:

=IF([Time Spent (Hours)]@row > 0, IF([% Comp]@row = 1, “Complete”, IF([% Comp]@row = 0, “Not Started”, “In Progress”)), “Not Started”)

As, I think @Genevieve P pointed it out on another thread, you'll note that quotes here are a little bit curved as the formula is being paste from Word. They should be straight.

Try copy/paste this formula:

=IF([Time Spent (Hours)]@row>0; IF([% Comp]@row = 1, "Complete", IF([% Comp]@row = 0, "Not Started", "In Progress")), "Not Started")

That one is a little tricky, but the problem comes from the quotes.

Nigel Michell

Thanks @David Joyeuse

I copied the formula and then I manually changed the quote marks to ensure that they were correct but Smartsheet still says the formula is Unparseable.

This is what I pasted:

=IF([Time Spent (Hours)]@row>0; IF([% Comp]@row = 1, "Complete", IF([% Comp]@row = 0, "Not Started", "In Progress")), "Not Started")

Looking at this now I can see what the problem is - there is a semi colon rather than comma after the first 'If'!

It now works!

Many thanks again for your time

All comments

Nigel Michell

@David Joyeuse

Thanks so much for your input - I am out of the office for the next couple of days but I look forward to reviewing your comments.

Thank you again.

Nigel

Nigel Michell

@David Joyeuse

Thanks for the feedback it was really useful - as the template is work in progress, I have not locked any columns but I will do so once it is finalised.

Can you explain more about Row 4B that you added in relation to basing the conditional formatting on it and also what the =count(ancestors())+1 does.

I like your formula for 'Calculated Status' however I cannot get it to work - it indicates #UNPARSEABLE - not sure why as I have checked the column names and brackets and they all seem fine.

Thanks again for your input.

Nigel

Nigel Michell

@David Joyeuse

Would you be able to comment on my questions above regarding your review of our Template?

Many thanks

Nigel

David Joyeuse

Hi @Nigel Michell

Sorry for the delay, I've been a little bit too busy to come back to you.

Now regarding the calculated status column:

=IF([Time Spent (Hours)]@row > 0, IF([% Comp]@row = 1, “Complete”, IF([% Comp]@row = 0, “Not Started”, “In Progress”)), “Not Started”)

As, I think @Genevieve P pointed it out on another thread, you'll note that quotes here are a little bit curved as the formula is being paste from Word. They should be straight.

Try copy/paste this formula:

=IF([Time Spent (Hours)]@row>0; IF([% Comp]@row = 1, "Complete", IF([% Comp]@row = 0, "Not Started", "In Progress")), "Not Started")

That one is a little tricky, but the problem comes from the quotes.

Nigel Michell

Thanks @David Joyeuse

I copied the formula and then I manually changed the quote marks to ensure that they were correct but Smartsheet still says the formula is Unparseable.

This is what I pasted:

=IF([Time Spent (Hours)]@row>0; IF([% Comp]@row = 1, "Complete", IF([% Comp]@row = 0, "Not Started", "In Progress")), "Not Started")

Looking at this now I can see what the problem is - there is a semi colon rather than comma after the first 'If'!

It now works!

Many thanks again for your time

David Joyeuse

Ah sorry, old french typing habits :P

Nigel Michell

@David Joyeuse

Yes - I work in Quebec and I am always getting confused between ',' and ';'!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.