#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Sum children networkdays

edited 12/09/19

I am working on a complex formula that I won't get into the details of but I am having a problem with one small part of it.

I need to check the children of a parent row to see if the task has already started but not ended. Column names are {Actaul Start] and [Actual Finish]. Then if this criteria is met I want to get the NETWORKDAYS between the finish day and TODAY() for each of the children that meet that criteria and then SUM them. I would perfer to do this without creating a bunch of new columns.

Any help? Thanks.

• ✭✭✭✭✭✭

Joseph,

I don't believe you will find a solution that does not have at least one new column, as the SUM, SUMIF, and SUMIFS will not sum calculated values

I assume:

1. you are looking at

=NETWORKDAYS(TODAY(), [Actual Finish]23)

which is positive for dates in the future, negative for dates in the past.

2. finished is based the finish date, not on a Completed column of some sort

Based on that, this would go into the new column:

=IF(TODAY() >= [Actual Start]23, IF(TODAY() < [Actual Finish]23, MAX(NETWORKDAYS(TODAY(), [Actual Finish]23), 0), 0), 0)

for row 23.

The parent is just =SUM(CHILDREN())

This formula also works to show why a non-past due task is not part of the sum:

=IF(TODAY() >= [Actual Start]58, IF(TODAY() < [Actual Finish]58, MAX(NETWORKDAYS(TODAY(), [Actual Finish]58), 0), "Started and Finished"), "Not Started")

Hope that helps

Craig

• The past due tasks are handled in the larger part of the formula that I mentioned. I will see how this formula works in. Adding new columns to all of our smartsheets would be a huge pain and I would like to avoid it but I may not be able to. Its too bad you can't set variables in a formula.

This discussion has been closed.