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.

Help with Duration by assigned to formula



I need to sum the duration colums by assigned to if the status is complete.  I am having so many errors when trying to do this.  Can anyone help?




  • JamesR
    JamesR ✭✭✭✭✭✭



    =SUMIF([Assigned To]:[Assigned To], [Assigned To]1, NewColumn:NewColumn)


    You will need to add a column with a formulae that gived you the number part of the duration only.  You will need to use text functions Left, Len and Right to do this.


    If you copy the formulae down then agans each name you will get the total days for that person.


    I hope this helps



  • That works for the grand total for the duration but I want a total duration for assigned to.

    Total duration

    =SUMIF([Assigned To]:[Assigned To], [Assigned To]1, Duration:Duration)

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    edited 04/09/16



    Be careful:

    1. if you have summary tasks.

    The Duration column will include the total of the children/sub-tasks.


    2. if you complete something early.

    The % Complete does not update the end-date if you complete something today that was due tomorrow.

    As such, your durations may be misleading.





  • JamesR
    JamesR ✭✭✭✭✭✭



    I think we need some clarification:

    What I suggested would give you the Total duration for an individual (Duration being Work time) for the Project.  It would repeat the sum each time they appear in the Assigned to column.


    Please be more specific, are you tlking Lapsed time rather than duration in which case you need End date - Start Date which included weekend  and other non working time.



  • I got it to work by using: 

    =SUMIFS(Duration1:Duration290, [Assigned To]1:[Assigned To]290, "LChristine Salmon", Status1:Status290, "Completed")

  • With your help I have been able to get my formula working.  Now I want to add if Start is greater than a certain date but keep getting errors.


    Start is a date field.


    =SUMIFS(Duration1:Duration290, [Assigned To]1:[Assigned To]290, "Lisa Whalen", Status1:Status290, "Completed")

This discussion has been closed.