Sum(Children())

Nigel Michell · 08/21/20

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

David Joyeuse · 09/02/20

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 · 09/02/20

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 · 08/21/20

Hi @Nigel Michell

That would be:

=IF(COUNTIF(CHILDREN(),NOT(ISBLANK(@cell)))=COUNT(CHILDREN([Main Column]@row)),SUM(CHILDREN()),"")

But reading you the problem does not lies in the SUM(CHILDREN()) but in the fact that when the task as not started, the remaining effort is blank.

Following your logic, since you want to display what remains to do, shouldn't be the remaining effort be 100% when the task is not started?

Nigel Michell · 08/21/20

Hi @David Joyeuse

Thanks for such a quick reply - it looks very complicated!

When I try to use it, it comes back as #UNPARSEABLE - am I doing something wrong?

David Joyeuse · 08/21/20

@Nigel Michell

Maybe it comes from [Main Column], I used it as a placeholder to designated the name of your main column on your sheet. That's a thing I often due because for whatever reason, comparing COUNTIF(CHILDREN()) and COUNT(CHILDREN()) often returns error to me if I do not specify another column...

Nigel Michell · 08/21/20

Sorry - yes - did not notice that!

Will convert the value to the Column Name

Nigel Michell · 08/21/20

It still returns 0

David Joyeuse · 08/21/20

It seems all of your children are at 0, so getting 0seems fine to me.

Watch out, for Smartsheet being Blank is different than being equal to 0.

So maybe you try this workaround:

=IF(COUNTIF(CHILDREN(),OR(NOT(ISBLANK(@cell)), @cell<>0))=COUNT(CHILDREN([Main Column]@row)),SUM(CHILDREN()),"")

Here we'll check if the cell is blank or if children is equal to 0 and SUM will happen only when all cell will be filled with a number that is not 0.

Nigel Michell · 08/21/20

@David Joyeuse

Sorry I am obviously not explaining this well. If the value in Remaining Effort is 0 then the Task is complete and the 0 should be calculated.

The issue relates to the cells under the Orange row. The Child Tasks under the Orange Parent row have not started therefore the cells are empty. If the Child tasks are blank then the Parent Task should also be blank and not 0.

Is it possible to come up with a formula that does not put a 0 in the Parent Task if all the Child Tasks are blank?

Many thanks.

Nigel

David Joyeuse · 08/22/20

@Nigel Michell

Which formula do you have in the children rows? The cell being empty, does not necessarily mean that the cell is blank.

Until then, let's try this:

=IF(COUNTIF(CHILDREN(),ISNUMBER(@cell))<>0, SUMIF(CHILDREN(), ISNUMBER(@cell)), "")

This will check for if you have numbers or not in children cell. If that count is equal to zero, you'll display nothing. Otherwise, you'll sum Children that are numbers only so you don't get any errors.

Nigel Michell · 08/22/20

@David Joyeuse

There is no formula in the Child rows - the value is manually entered based on the predicted time remaining for the Task.

Maybe I am expecting too much. Your new formula works if all the Child cells are empty but if one is 0 then the Parent Task is recorded as 0 and the Status of the Task is updated to complete. This is the formula that I am using for this field:

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

Thanks for your continued suggestions.

Nigel Michell · 08/22/20

@David Joyeuse I suppose the obvious way to fix the problem is to manually copy the value from the 'Effort' column to the 'Remaining Effort' column so that the child cells are not null but I wanted to see if there was a formula that could be used to prevent this.

David Joyeuse · 08/22/20

@Nigel Michell

OK, we should work with your status column then.

=SUMIFS(CHILDREN(),CHILDREN([Status of the Task Column]), <>"Not Started")

Nigel Michell · 08/22/20

@David Joyeuse

I also need the other Statuses of 'In Progress' and 'Complete'?

David Joyeuse · 08/22/20

Not really, as it should be designated to work only when things are not started according to what you previously said.

But as a whole, I believe it would be better to automate the whole thing so you don't have to enter data manually somewhere in the process, except maybe in the [Time Spent (Hours)] column (though we could use some formulas to gather this from other sheets as people works on stuff).

Could you provide us a screenshot of the whole sheet, with no sensitive data or confidential ones within it?