Sum of durations excluding the parents

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


Answers

  • Bassam Khalil
    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


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Dinesh
    Dinesh
    edited 07/16/21

    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
    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.