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.

 

[buffalofan19]

Okay, we're close. That formula works, so long as there is a value in each shift for the employee.

Shift Example.xlsx
	A	B	C
1	Employee:	Shift	Rate
2	Employee 1	8-12	$9
3	Employee 2	8-12	$12
4	Employee 3	12-7	$10
5	Employee 4	11-4	$8
6	Employee 6	7-3	$7
7	Hours:	28
8	Labor$:	250
Sheet1

Cell Formulas
Range	Formula
B7	B7	=SUMPRODUCT(INDEX(Sheet2!$B$2:$B$5,N(IF({1},MATCH($B$2:$B$6,Sheet2!$A$2:$A$5,0)) )))
B8	B8	=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)

Named Ranges
Name	Refers To	Cells
Hours	=Sheet2!$B$2:$B$5	B7:B8
Shift	=Sheet2!$A$2:$A$5	B7:B8

If an employee is not working that day, they need to be a blank, and then the formula goes to the #N/A error.

Shift Example.xlsx
	A	B	C
1	Employee:	Shift	Rate
2	Employee 1	8-12	$9
3	Employee 2	8-12	$12
4	Employee 3	12-7	$10
5	Employee 4	11-4	$8
6	Employee 5		$7
7	Employee 6	7-3	$7
8	Hours:	#N/A
9	Labor$:	#N/A
Sheet1

Cell Formulas
Range	Formula
B8	B8	=SUMPRODUCT(INDEX(Sheet2!$B$2:$B$6,N(IF({1},MATCH($B$2:$B$7,Sheet2!$A$2:$A$6,0)) )))
B9	B9	=SUMPRODUCT(INDEX(Sheet2!$B$2:$B$6,N(IF({1},MATCH($B$2:$B$7,Sheet2!$A$2:$A$6,0)) )),$C$2:$C$7)

 

[KRice]

See if this internal error trap with IFERROR addresses the issue.

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		10		0	0
5	4	11-4	8		5	40
6	5	7-3	7		8	56
7					21	180
8		Hours	21
9		Labor	180
Sheet1

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

 

[buffalofan19]

That worked. Thanks! Also, it appears I do not need to make it an array formula with Excel 365.

 

[KRice]

That's good news...you're welcome. Yes, you're correct...Excel 365 handles array formulas automatically, without the need for special entry.

 

[Amit Tandon]

An easy way apparantly:

Sumif_Countif_ArrayMatch.xlsx
	A	B	C
1
2	Employee 1	8-12	$9
3	Employee 2	8-12	$12
4	Employee 3		$10
5	Employee 4	11-4	$8
6	Employee 6	7-3	$7
7
8		Hours	$21
9		Labor	$180
Sheet1

Cell Formulas
Range	Formula
C8	C8	=SUM(COUNTIF(Sheet1!B2:B6,Sheet2!A2:A5)*Sheet2!B2:B5)
C9	C9	=SUM(SUMIFS(C2:C6,Sheet1!B2:B6,Sheet2!A2:A5)*Sheet2!B2:B5)

 

[buffalofan19]

That works too, but for some reason, it slows down my master spreadsheet, where the SUMPRODUCT one does not.

 

[Amit Tandon]

That works too, but for some reason, it slows down my master spreadsheet, where the SUMPRODUCT one does not.

In case you are referring to my formulas - in my opinion, using COUNTIF / SUMIF is a more efficient method and should reduce your processing time. And each formula uses only a single COUNTIF or SUMIF with SUM.