Sumproduct a column's worth of lookups

[buffalofan19]

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.

 

[JustynaMK]

Which Excel are you working with? If it's Excel 365, then your life will be easier thanks to enhancements introduced to array formulas. You'd simply need to use:

=SUM(VLOOKUP(shift,B2:C4,2,0)*hours)

The result:

 

[buffalofan19]

I do have 365, but that is not working for me for some reason (coming up as #N/A), regardless of whether or not I make it an array formula (not sure if that matters). This is also a scaled down version. I actually have 7 columns worth of shifts, one for each day of the week, and I want to sum the hours and Labor$ for each day.

 

[KRice]

I've deleted my suggestion...I think it has some problems.

 

[buffalofan19]

Now I get a #VALUE! error. Is that because the Shift on the first sheet is in text?

 

[JustynaMK]

Thanks for confirming - I suspect your Office 365 doesn't support this new functionality yet How about this array formula (Ctrl+Shift+Enter):

=SUM(IF(shift=Sheet1!B2:B4,Sheet1!C2:C4,0)*hours)

If both "shift" ranges on Sheet1 and Sheet2 are of the same datatype, then there shouldn't be any issues.

 

[buffalofan19]

Still getting a #VALUE! error...

 

[JustynaMK]

Do you have #VALUE! error already somewhere in your "rates" or"hours" ranges? Alternatively, could you also please check if your "rates" and "hours" ranges contain only numbers?

For example, if I put text into my "hours" range (Sheet2), I get a #VALUE! error. Make sure that your named ranges are only containing numbers.

Sheet2:

Sheet1:

 

[buffalofan19]

I think that's the problem. I normally reference the blank cell below the bottom of the list in case someone needs to add to the list. There will also be blanks in the employee sheet as not every employee has a shift on every day. Is there a way to get around that?

EDIT: This also only seems to work if the lists on the second sheet contain the same number of rows as the data on the first sheet. If I add another shift (i.e. a row) to either sheet, I get an #N/A error. I need to be able to add and delete rows without breaking the formula.

 

[KRice]

Here is another approach...it's ugly, but I think it works. I added some columns at right as a double check, but they are not needed. I did not use named ranges, but those could be substituted in. I've added an extra shift definition and a couple more employees to check functionality with arrays of different sizes.

MrExcel20200716b.xlsx
	A	B	C	D	E	F
1	Employee	Shift	Rate		Check
2	1	8-12	9		4	36
3	2	8-12	12		4	48
4	3	12-7	10		7	70
5	4	11-4	8		5	40
6	5	7-3	7		8	56
7					28	250
8		Hours	28
9		Labor	250
Sheet1

Cell Formulas
Range	Formula
E2:E6	E2	=VLOOKUP(B2,Sheet2!$A$2:$B$5,2,0)
F2:F6	F2	=C2*E2
E7:F7	E7	=SUM(E2:E6)
C8	C8	=SUMPRODUCT(INDEX(Sheet2!$B$2:$B$5,N(IF({1},MATCH($B$2:$B$6,Sheet2!$A$2:$A$5,0)) )))
C9	C9	=SUMPRODUCT(INDEX(Sheet2!$B$2:$B$5,N(IF({1},MATCH($B$2:$B$6,Sheet2!$A$2:$A$5,0)) )),$C$2:$C$6)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

MrExcel20200716b.xlsx
	A	B
1	Shift	Hours
2	8-12	4
3	11-4	5
4	12-7	7
5	7-3	8
Sheet2