Manpower Projections w/ Multiple Schedules

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
    Mark Cronk ✭✭✭✭✭✭

    Hi @TJ Breslin ,


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



    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Genevieve P.
    Genevieve P. Employee Admin

    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?



  • 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?



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!