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 Duration but without Milestones + Lag

Hi All

Is it possible to use a formula or some other function to Sum the duration of a project but not include time associated with lag or between a task being completed and its milestone? i.e I only want to sum tasks which have a duration but I have used indents to manage my project which creates durations including unallocated time between tasks.


I want to sum this so I can estimate hours which can be billed in a project. I can't bill for time when we're waiting for information to come back from other consultants so I want this to be excluded from the calculation.

Thanks in advance.





  • Atus Bartal
    Atus Bartal ✭✭✭✭✭✭


    have you tried simply sum up the days in the Duration column for each task? You can insert a new column and put this formula in all parent rows (but not in "grandparent" rows, if relevant): =SUM(CHILDREN(Duration1))   [1 refers to the first - parent - row]

    It counts and sum up the time (day) allocation for the tasks themselves, independent of the calculated length of the project (which is determined by other factors: lags, task overlapping, etc.). 

    Hope it helps.


  • Office Norton Mayfield
    edited 08/09/16

    Thanks Atus. That's worked perfectly!


    To take it one step further.... Is there a way to sum tasks in parent rows but only tasks allocated to a particularl individual?


    Ideally it would be good to do it as a single sheet but alternatively I guess I could produce a report for each person for each project they're involved in?


    Thanks in advance.



  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Have you tried the SUMIF formula?

    That sounds like what you are looking for in the follow up.


    Reports do not allow additional formulas, so all of the calculations are on the sheet level.


    Hope this helps.



  • Hi Craig


    That does sound spot on but I'm having difficulty translating the SmartSheet example equations into my specific example:

    Smartsheet suggests:

    Syntax: SUMIF(criteria_range, criteria_value, sum_range)


    =SUMIF(Done:Done, 1, Cost:Cost)
    Result: 300


    And I take this to mean if Done is equal to 1 then it will SUM the Cost column.  I have therefore tried the following:


    =SUMIF(Assigned To:Assigned To, Max Everett, Task Duration:Task Duration)


    Meaning if the task is assigned to Max then sum the task duration. However, it's coming back as unparseable. Any thoughts on where I'm going wrong?


    The programme has parent and child rows if that has something do with it.




  • Please add my vote as well. I will rather resend than create a new request if I know the receiver has not updated the form.

    I hope someone see this.


This discussion has been closed.