adding another column to calculate hours

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"))))

Best Answer

  • Aditya Desale
    Aditya Desale ✭✭
    Answer ✓

    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.

Answers

  • Aditya Desale
    Aditya Desale ✭✭
    Answer ✓

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!