How to get a roll-up of total workers needed on a project, on a day-to-day/week-week basis?

Justin Prince

I've built a sheet that allows multiple foreman(activity owners) to build out there task for the next few weeks. There's an activity owner column, start column, end column, duration column, and # of workers needed column. (Pic Attached) I would like to find a way to track how many workers each foreman would need on a weekly basis. I would like to make it so even if there's 5 tasks that week, I don't just get a total of the 5 tasks. If task 1 and 2 start on Monday and finish on Tuesday and each have 2 workers assigned to them, then task 3 and 4 start on Wednesday and finishes on Friday with 2 workers. Then Task 5 lasts Monday-Friday for 1 worker. I would like to see that really we only need 5 workers for the week, since the same workers from task 1 and 2 could roll into task 3 and 4, then there's one that is on task 5 all week. I'd like to see a week by week break out for each activity owner. I would like to see it in the same sheet, but don't know what formulas I need to use, or not sure if it's better to accomplish this by utilizing a separate report.

Julio S.

Hi @Justin Prince,

Although I couldn't come up with an individual formula that would count the number of unique workers needed in a given week. I was able to calculate the amount of unique workers that was needed each day of the week by creating different fields for each day of the week (Sunday-Saturday) and adapting the following SUMIFS formula for each given day (Sunday feb 13th in my example below): 

=SUMIFS([# of Workers needed]:[# of Workers needed], Start:Start, <=DATE(2022, 2, 13), Finish:Finish, =DATE(2022, 2, 13)) 

This would offer you an overview of what's the maximum number of workers needed any given day of the week which may be helpful when it comes to calculating the number of unique resources used. Note that for this solution you may add additional Columns to your sheet or use Sheet summaries.

I hope this can offer some guidance when building up your solution.

Cheers! 

Julio