Sum of durations excluding the parents

Dinesh

Here's a demo project and I am trying to build queries to extract some data and could do with some help with building the appropriate queries.

1. I'd like to query the duration of all the tasks in the sheet. in this case that number would be 20 weeks. However I don't want any of the parent item durations to be included - so I don't want Project1 to be included in the sum since that results in double counting.
2. Similarly I would like to sum up the duration for all tasks that don't have owners assigned. In the example below that should be 6 weeks. Again I am looking for a query that excludes the "Project 1" row.
3. Finally is there a way to reflect effort vs calendar time (which is what the duration field appears to be)

Any help would be appreciated. I've just started using smartsheets

Bassam Khalil

Hi @Dinesh

Hope you are fine, the following is your formula:

1- don't want Project1 to be included in the sum since that results in double counting :

=SUM(CHILDREN(Duration:Duration))

2-  sum up the duration for all tasks that don't have owners assigned

=SUMIFS(CHILDREN(Duration:Duration), CHILDREN([Assigned To]:[Assigned To]), ISBLANK(@cell))

the following screenshot shows the result

Dinesh

Hi Bassam,

thanks for that and I apologize when constructing the demo, I might have missed one detail. In some cases there are two and potentially even more levels of nesting - see below. In such cases even the duration for "Milestone" gets added.

Any thoughts on how I can ensure only the leaf nodes are included in the calculation

Rich Stowell

Dinesh,

Consider using a "helper' column. I create a column "LVL" (for Level) with the following column formula:

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS([Task Name]@row)) + 1, "")

This will allow you to count only those rows that do not have children.

=SUMIF(LVL:LVL,"",Duration;Duration)

And for those with no one assigned

=SUMIFs(,Duration;Duration, LVL:LVL,"",[Assigned to]:[Assigned to]@row, ISBLANK())

You can also use the helper column to set up Conditional Formatting for all rows with values in the LVL column for easier viewing.