Sum Duration but without Milestones + Lag

Office Norton Mayfield

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.

Alan

 

 

Atus Bartal

Alan,

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.

Atus

Office Norton Mayfield

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.

 

Alan

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.

 

Craig

Office Norton Mayfield

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)

Examples:

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

 

Thanks

Alan

idiakite32106

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.

Thanks.