Manpower Loading Schedule
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
Comments

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...)?

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

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.

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.

That worked! Thanks!
How does the "@cell" work?

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

Brandon were you able to get your spreadsheet to work?
Help Article Resources
Categories
Check out the Formula Handbook template!