Manpower Loading Schedule

Brandon Clark

I am trying to create a Project(schedule) that has a manpower loading functions. I am almost there but I am stuck with trying to figure out how to see how many crew members I have on a certain days by suming them up. 

For example I would like to have a list of dates and then show how many crew members working on those dates. The examples should show 

4/29 = 15

4/30 = 15

5/1 = 50

5/2 = 50

5/3 = 35

 

Paul Newcome

Would it just be the sum of the manpower by date or would you be factoring additional criteria such as type (HVAC, Electric, Framing, etc...)?

Brandon Clark

It would be the sum of crew size for each individual date. So anytime one date appears within the start to finish date range and there is a crew assigned to that range I would like to sum only those crews. 

Example:

4/29 - 5/2 crew size 2

4/30 - 5/3 crew size 2

 

From the data above I would want to sum for each date.

4/29 = 2

4/30 = 4

5/1 = 4

5/2 = 4

5/3 = 2 

 

Paul Newcome

Ok. So you would want to use something along the lines of 

 

=SUMIFS([Crew Size]:[Crew Size], [Start Date]:[Start Date], @cell <= Date@row, [Finish Date]:[Finish Date], @cell >= Date@row)

 

This says to sum everything in the Crew Size column where the Start Date is less than or equal to the date specified and the End Date is greater than or equal to the date specified.

Brandon Clark

Thanks. It should have returned a 4 but it shows 0. Not sure why?

I think this is the right track but I am trying to look at the dates in a range compared to a single date. 

The attached pictures may help explain. 

Paul Newcome

=SUMIFS([Craft 3 Crew Size]:[Craft 3 Crew Size], Start:Start, @cell <= Start270, Finish:Finish, @cell >= Start270)

 

You are using the wrong information in your formula. Try the formula above exactly as is leaving the @cell references and everything.

Brandon Clark

That worked! Thanks!

How does the "@cell" work? 

Paul Newcome

Happy to help! 

 

The @cell reference basically just tells the formula to look at each individual cell within the range to compare to the criteria instead of the entire range as a whole.

 

Here is an explanation from Smartsheet themselves:

 

"Perform calculations in formulas that use SUMIF(), SUMIFS(), COUNTIF(), and COUNTIFS(), you can use the @cell parameter in the criteria of the function. The @cell parameter performs a calculation on each row at the same time that the primary function (SUMIF for example) is evaluating the criteria in the range."

Wasaga02

Brandon were you able to get your spreadsheet to work?