Manpower Projection Formula
I have multiple schedules for different projects with the same base information:
Task Name (Primary)
Start Work Date
End Work Date
Duration (Dependencies are enabled)
Manpower Needed
Many tasks have durations that overlap with other tasks.
I want a separate manpower projection sheet that tells me each day what the total manpower would be needed so I can forecast months/years in advance how many people I would need to do my jobs.
Example:
5/1  5/3: Total Manpower Needed = 10
5/2  5/4: Total Manpower Needed = 6
5/1 Total Manpower Needed = 10
5/2 Total Manpower Needed = 16
5/3 Total Manpower Needed = 16
5/4 Total Manpower Needed = 6
I have been trying the =SUMIFS formula but I can't figure out how to calculate the manpower needs between the SWD (Start Work Date) & EWD (End Work Date)
Current Formula: =SUMIFS({CMH076  Active Schedule Estimated Manpower}, {CMH076  Active Schedule SWD}, SWD@row)
I really appreciate any help!
Answers

 You can calculate the duration between the SWD and EWD.
 In order to calculate the Manpower, you can find the average time one worker takes to complete the task in hours.
Formula: CEILING(Duration/ (Hours_per_worker *24), 1)
I am anticipating, if this would work.
Thanks,
Deepanshu Sharma

Sharma,
Thank you for the quick response. This formula did not work for me though.
Hours worked or to complete a task does not matter in this application. I strictly just need to know on any given day throughout the year how much manpower I need. 
don't forget that with sumifs you can have multiple criteria:
for manpower on a specific date try something like this: =SUMIFS({manpower},{startDate},dateYouCareAbout@row,{endDate},dateYouCareAbout@row)
For manpower between two different dates, this:
=SUMIFS({manpower},{startDate},">=" + startDateYouCareAbout@row,{endDate},"< =" + endDateYouCareAbout@row)
Help Article Resources
Categories
Check out the Formula Handbook template!