sum of work hours per week

Nathan White

I have a sheet that has hours our employees are going to be offsite, sometimes multiple employees will be on different sites during the same week and i need the hours they are going to be offsite to show up on the departments "Capacity Report"….. so i need a formula that uses [work week]@row on the destination sheet to pull all hours from the source sheet and give a sum of total hours per week while filtering the hours to the correct department that is selected via dropdown on the source sheet.

bisaacs

Hey @Nathan White,

I think I understand 95% of it, based on what you've provided here's what I understand you're trying to do:

• You want to match every 8 hour day amount in the first sheet where the week start matches the Work Week column in the second sheet and put that number in the [Offsite Supervision Hours]
• Then you want to take that total, and divide it by the [Total Hours Available] in sheet 2, to get a Capacity %
• I'm not sure what the Capacity Report is, are you saying that there's a Capacity Report for each supervisor department, and you want it filtered by that as well?

Assuming the above is correct, here's what I would do:

• In the [Offsite Supervisor Hours], use the following formula:

=SUMIFS([8 hour days]:[8 hour days], [Week Start]:[Week Start], [Work Week]@row, [Supervisors department]:[Supervisors department], "GSO Fabrication")

• Then, in the [Capacity %] column, put:

=[Offsite Supervisors Hours]@row / [Total Hours Available]@row

You'll need to change the column ranges to references, but hopefully this is what you're looking for?

bisaacs

Hey @Nathan White,

Could you provide screenshots of your sheets so we have a better idea of how they're laid out? Please be sure to obscure any sensitive information

Nathan White

Here you are@bisaacs

I need the hours in [8 hour days] from the sheet above to be added together if they fall in the same week and have it populate to the matching work week in the sheet below, using the "Supervisors Department" column to send it to the correct "Capacity Report" in [Offsite Supervision Hours]. the 64 hours in the SS is from my testing and it isnt correct…

bisaacs

Hey @Nathan White,

I think I understand 95% of it, based on what you've provided here's what I understand you're trying to do:

• You want to match every 8 hour day amount in the first sheet where the week start matches the Work Week column in the second sheet and put that number in the [Offsite Supervision Hours]
• Then you want to take that total, and divide it by the [Total Hours Available] in sheet 2, to get a Capacity %
• I'm not sure what the Capacity Report is, are you saying that there's a Capacity Report for each supervisor department, and you want it filtered by that as well?

Assuming the above is correct, here's what I would do:

• In the [Offsite Supervisor Hours], use the following formula:

=SUMIFS([8 hour days]:[8 hour days], [Week Start]:[Week Start], [Work Week]@row, [Supervisors department]:[Supervisors department], "GSO Fabrication")

• Then, in the [Capacity %] column, put:

=[Offsite Supervisors Hours]@row / [Total Hours Available]@row

You'll need to change the column ranges to references, but hopefully this is what you're looking for?

Nathan White

https://community.smartsheet.com/discussion/comment/443509#Comment_443509

Thank you!

That does what i need! i accidently hit the wrong button when i was asked if this answered my question. sorry about that