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

Find more posts tagged with

Construction

Formulas

Gantt View

Comments

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?

Sign in to join the conversation!

Quick Links

Trending discussions

Return a value based on 2 matching criteria, one of which is a dropdown menu.
I am working on a formula that will match a date from my reference sheet to my working sheet, and a word from a dropdown menu in a reference sheet and return the word "Completed". None of these work
Date Function Returning #INVALID COLUMN VALUE - even in clean new sheets
Hi all. I’m running into an issue that I can’t seem to resolve, even after a lot of testing. Hoping someone here might be able to help. Basically, any formula I use that involves a date function, like ISDATE() or TODAY(), is returning #INVALID COLUMN VALUE — even in completely new, empty sheets. Here’s what I’ve tested so…
Formula Help - Phase identifying
Good morning :) I'm trying to figure a way that the row is labeled by the phase of the project that the line is in without having to manually do it. Please see below expected output for 2 phases. I have also attached an excel sheet. I would greatly appreciate any help on this please.