buffalofan19
New Member
- Joined
- Jun 26, 2009
- Messages
- 33
I have a text list of values that represent workers' shifts, with a lookup table that corresponds to the number of hours they work in that shift. On the second sheet, I named the ranges "Shift" and "Hours"
Sheet1:
Column A Column B Column C
Employee: Shift: Rate
Employee 1 8-12 $9
Employee 2 11-4 $12
Employee 3 12-7 $10
Hours: 16
Labor$ $166
Sheet2:
Column A Column B
Shift Hours
8-12 4
11-4 5
12-7 7
I am trying to get the total number of hours in Sheet1, of which the correct answer is 16 (4+5+7), and labor dollars in Column B of Sheet1, of which the correct answer is $166 ((4x$9)+(5x$12)+(7x$10)). I figured out the hours part: =sumproduct(sumif(shift,Sheet1!B2:B4,hours)). I still need to get the Labor$ part. I have a feeling that Sumproduct is the solution here, but I can't figure out combinations that work.
Sheet1:
Column A Column B Column C
Employee: Shift: Rate
Employee 1 8-12 $9
Employee 2 11-4 $12
Employee 3 12-7 $10
Hours: 16
Labor$ $166
Sheet2:
Column A Column B
Shift Hours
8-12 4
11-4 5
12-7 7
I am trying to get the total number of hours in Sheet1, of which the correct answer is 16 (4+5+7), and labor dollars in Column B of Sheet1, of which the correct answer is $166 ((4x$9)+(5x$12)+(7x$10)). I figured out the hours part: =sumproduct(sumif(shift,Sheet1!B2:B4,hours)). I still need to get the Labor$ part. I have a feeling that Sumproduct is the solution here, but I can't figure out combinations that work.