Summarize duration in a project sheet by week/date range?

Gordon

Is there a way to summarize the DURATION data in a resource-enabled project sheet by week or a given date range? i.e., I want to see total hours for the last week by person. I already have a helper column inserted which converts DURATION to hours.

Thanks!

M Underbrink

=SUMIF(AND([DateColumn]@row < TODAY(), [DateColumn]@row TODAY(-7)), [HoursColumnYouWanToSum])

Something similar to this could work. If you wanted to do it for multiple people on a sheet, you'd have to have a rollup/calculation grid, with all of their names on it, but have the sumIF check for the additional condition in which the "Assigned to" column checked if they were that person

=SUMIF(AND([AssignedToColumFromOthersheet]=[AssignedTo]@row, [DateColumn]@row < TODAY(), [DateColumn]@row TODAY(-7)), [HoursColumnYouWanToSum])

Let me know if further clarification is needed!

Gordon

@M Underbrink Thank you for the suggestion. Regarding the [Date Column] reference, because these are resource-enabled project sheets, I have two columns: [Start Date] and [End Date].

Using your formula as a starting point, do you think this would work if I made it a SUMIFS instead with an AND for both dates:

=SUMIFS(AND([Start Date]@row < TODAY(), [Start Date]@row <TODAY(-7)),AND([End Date]@row < TODAY(), [End Date]@row <TODAY(-7)),[Duration])

The only problem I keep running into is how to get the [Duration] to be only for the week period if the [Start Date] and [End Date] are longer or shorter than one week. At first I thought I could use NETWORKDAYS, but I couldn't get the formula logic to work based on the [Start Date], [End Date] and last week parameters.

M Underbrink

Starting to get a little too into specifics for me to be able to solve through a chat forum.... 😉

When things start getting too complex, I start to look at why I'm measuring something and if there is an easier way to measure it. If you never want the duration of any given column to count beyond 40 hours for your formula, then I would focus my efforts on restricting the duration column I'm summing to a max of 40 through formulas. Would that work for your purposes? You could have a second column next to Duration that "Max 40 Duration" with the column formula as follows,

=IF([Duration]@row<40, [Duration], 40)

Also, I don't think my solution works as is - it may need some additional tweaking....