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
-
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 bothOSLD
andOSLD2
if they belong to the "LV Manufacturing" department.
Answers
-
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 bothOSLD
andOSLD2
if they belong to the "LV Manufacturing" department. -
Thank you so much
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!