adding another column to calculate hours

Nathan White

Below are the references and the formula I am using to calculate "Offsite supervision hours" and automatically sum any hours that fall in the same week.

But I also need the sum of the hours to go to different sheets based off which department/s are assigned…

LVM8HD, LVM10HD, LVM12HD are referencing a cell that contains how many hours they are working during a week so the formula knows what to multiple by.

OSLWS is referencing a date column that takes the day the work begins and outputs the weeks start date.

OSLD is referencing the Department the worker is from

OSLD2 is a reference that I'm trying to use for when a second department is going to the same job

=IF({LVMWH} = 8, SUMIFS({LVM8HD}, {OSLWS}, [Work Week]@row, {OSLD}, "LV Manufacturing"), IF({LVMWH} = 10, SUMIFS({LVM10HD}, {OSLWS}, [Work Week]@row, {OSLD}, "LV Manufacturing"), IF({LVMWH} = 12, SUMIFS({LVM12HD}, {OSLWS}, [Work Week]@row, {OSLD}, "LV Manufacturing"))))

Aditya Desale

Hi there,

To sum up "Offsite supervision hours" and ensure they are allocated to different sheets based on departments, you can modify your formula to include checks for both primary and secondary department assignments. Here's an updated version of your formula:

=IF({LVMWH} = 8, 

     SUMIFS({LVM8HD}, {OSLWS}, [Work Week]@row, {OSLD}, "LV Manufacturing") + SUMIFS({LVM8HD}, {OSLWS}, [Work Week]@row, {OSLD2}, "LV Manufacturing"), 

   IF({LVMWH} = 10, 

     SUMIFS({LVM10HD}, {OSLWS}, [Work Week]@row, {OSLD}, "LV Manufacturing") + SUMIFS({LVM10HD}, {OSLWS}, [Work Week]@row, {OSLD2}, "LV Manufacturing"), 

   IF({LVMWH} = 12, 

     SUMIFS({LVM12HD}, {OSLWS}, [Work Week]@row, {OSLD}, "LV Manufacturing") + SUMIFS({LVM12HD}, {OSLWS}, [Work Week]@row, {OSLD2}, "LV Manufacturing"))))

This formula checks the work hours (LVMWH) and sums the hours from both OSLD and OSLD2 if they belong to the "LV Manufacturing" department.

Aditya Desale

Hi there,

To sum up "Offsite supervision hours" and ensure they are allocated to different sheets based on departments, you can modify your formula to include checks for both primary and secondary department assignments. Here's an updated version of your formula:

=IF({LVMWH} = 8, 

     SUMIFS({LVM8HD}, {OSLWS}, [Work Week]@row, {OSLD}, "LV Manufacturing") + SUMIFS({LVM8HD}, {OSLWS}, [Work Week]@row, {OSLD2}, "LV Manufacturing"), 

   IF({LVMWH} = 10, 

     SUMIFS({LVM10HD}, {OSLWS}, [Work Week]@row, {OSLD}, "LV Manufacturing") + SUMIFS({LVM10HD}, {OSLWS}, [Work Week]@row, {OSLD2}, "LV Manufacturing"), 

   IF({LVMWH} = 12, 

     SUMIFS({LVM12HD}, {OSLWS}, [Work Week]@row, {OSLD}, "LV Manufacturing") + SUMIFS({LVM12HD}, {OSLWS}, [Work Week]@row, {OSLD2}, "LV Manufacturing"))))

This formula checks the work hours (LVMWH) and sums the hours from both OSLD and OSLD2 if they belong to the "LV Manufacturing" department.

Nathan White

https://community.smartsheet.com/discussion/comment/444479#Comment_444479

Thank you so much