Manpower Resource Loaded Schedule - SUMIFS Formula Help Needed

Shane Wilder

Hello, I have created a resource loaded schedule based on manpower in Smartsheets. I have a separate "Metrics Calculation" sheet where I am calculating the manpower per day and per department. However, I am having trouble performing the calculation using a SUMIFS function. Once this number is calculated, I want to roll this information up into a Dashboard. I eventually want to display this information in a Chart that shows manpower needs each month.

Please see the snips below of the Production Schedule and Metrics Table with Formula. The formula is as follows:

=SUMIFS({Manpower}, {Start Date}, @cell <= [Date_]@row, {Finish Date}, @cell >= [Date_]@row, {Project Department}, CONTAINS("Drafting", {Project Department}))

Essentially, in this formula, I am trying to add the number in the Manpower Column if the date listed is in between the start and finish date and if the Department in the "Project Department" column is "Drafting" (eventually I want to do this same calculation for the other Departments - CNC Cut, Fabrication, etc.). At this point, it keeps returning a 0 and not adding the numbers in the Manpower Column.

Thank you for your help.

Paul Newcome

Have you double checked that all date columns are set as date type columns?

Lucas Rayala

Hi, you have a syntax issue with your contains -- you need to replace the {Project Department} with @cell:

CONTAIN("Drafting", @cell)

Your other @cell references aren't actually needed -- they are assumed in these instances (i.e. @cell<=[Date_]@row can just be <=[Date_]@row).

Let me know if this works!

Shane Wilder

Thank you for the feedback Lucas. I made the changes you suggested; however, I am still getting (0) for all dates where I am trying to return the manpower per day and per department. Any insight as to why this formula is not working?

Thank you for your help!

Paul Newcome

Have you double checked that all date columns are set as date type columns?

Shane Wilder

That was the issue! Thank you Paul! I knew it was something simple...

Paul Newcome

Happy to help. 👍️