Manpower Projections w/ Multiple Schedules

TJ Breslin

I have several projects and we are inserting the schedules into Smartsheet and linking those with various sheets. One thing I would like to create is a sheet that pulls in Crew Size from each job and for each date range display the labor needed. In each schedule, I placed a column for estimated hours and then used a formula to calculate crew size in the column next to it. So now in each schedule, I have the start date, finish date, and crew size all in separate columns. On a new sheet, I made 1 column dates starting from today onward, and then in the next I made each column a project schedule. I was trying to play with SUMIF formulas to sum the crew size column, if the date I placed in the first column fell within the date range of any task in the schedule. The formula that I think gets the closest to describing what I want to do is as follows:

=SUMIF({Test Schedule Range 5}:{Test Schedule Range 6},=[Date]@row,{Test Schedule Range 2})

where Test Schedule Range 5 is the Start Date, Test Schedule Range 6 is the End Date, Date is a sequential date in a row on the Crew Size sheet, and Test Schedule Range 2 is the Crew Size from the schedule.

Mark Cronk

Hi @TJ Breslin ,

Try:

=SUMIFS({Test Schedule Range 2}, {Test Schedule Range 5}, @Cell>=[date]@row, {Test Schedule Range 6}, @cell<=[Date]@row)

Work?

Mark

Genevieve P.

Hi @TJ Breslin

You indicated that Mark's formula didn't work for you, can you explain a bit further what happened when you tried it?

Since you have two criteria (two columns to check for a specific date), you will need to use SUMIFS (plural) and have it structured how @Mark Cronk showed.

The one thing I will note is that when you use @cell, please ensure that all the letters are lower-case. I can see in the formula above that there is a capital C, which would throw an error. Try it again like so:

=SUMIFS({Test Schedule Range 2}, {Test Schedule Range 5}, @cell>= [Date]@row, {Test Schedule Range 6}, @cell<= [Date]@row)

If this doesn't work, it would be helpful to know what you're seeing. Is it a specific error message, or an incorrect result?

Cheers,

Genevieve

TJ Breslin

Mark,

I tried that formula and it's spitting out 0 for everything. 1 minor correction was the first @cell needed a lowercase "c" or it gave me the UNPARSEABLE error. What is the @cell function and how does that work or what is the purpose of utilizing that?

Thanks,

TJ