Sum total based on data from another worksheet

[Mux99]

Hello

I would like to sum the total hours in Sheet1 Column I based on data from Sheet2 where each shift is listed with the amount of hours. I managed to do this with a helper worksheet but I'm sure there's an easier and faster way.

Sheet1	A	B	C	D	E	F	G	H	I
1		MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY	SUNDAY	TOTAL
2	Name1	Warehouse 0600-1400		Warehouse 0600-1400		Warehouse 0600-1400	Extra 0900-1500	Warehouse 0600-1400	38
3	Name2		Warehouse 0600-1400		Warehouse 0600-1400		Warehouse 0600-1400		24
4	Name3	Reception 0800-1300	Reception 0800-1300	Reception 0800-1300	Reception 0800-1300	Reception 0800-1300			25
5	Name4	Reception 1300-1700	Reception 1300-1700	Reception 1300-1700	Reception 1300-1700	Reception 1300-1700			20

Sheet2	A	B
1	Warehouse 0600-1400	8
2	Warehouse 1400-2200	8
3	Warehouse 2200-0600	8
4	Extra 0900-1500	6
5	Reception 0800-1300	5
6	Reception 1300-1700	4

 

[CA_Punit]

Try This Does it Work

Book1
	A	B	C	D
1				Lookup
2	NAME1	EIGHT		14
3	NAME2	SIX	SIX	12
4	NAME1		SIX	14
5	NAME3	EIGHT	EIGHT	16
6
7
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=SUM(IFERROR(LOOKUP(($A$2:$A$5=A2)*IFERROR(MATCH($B$2:$C$5,{"Eight","Six"},0),0),{1;2},{8;6}),0))

 

[Mux99]

Why not just change the CF rules to look at numbers instead?
At the end of the day numbers are numbers & should be be stored as such, it makes everything else so much easier.

Text will be the same as op and is needed. My only other option is to add a helper row with numbers under every row with data but this will double the size of my worksheet and slow down other formulas and CF.

 

[CA_Punit]

Is post 31 working????

 

[Mux99]

Is post 31 working????

Book1.xlsx
	A	B	C	D
1				TOTAL
2	NAME1	EIGHT		14
3	NAME2	SIX	SIX	12
4	NAME1		SIX	14
5	NAME3	EIGHT	EIGHT	16
6
7	EIGHT	8
8	SIX	6
Sheet2

Cell Formulas
Range	Formula
D2:D5	D2	=SUM(IFERROR(LOOKUP(($A$2:$A$5=A2)*IFERROR(MATCH($B$2:$C$5,$A$7:$A$8,0),0),{1;2},$B$7:$B$8),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Yes, it's working. Can you confirm that I'm linking the lookup table correctly?

Thanks again to both for all your help.

 

[CA_Punit]

This formula will not result in correct answer as look up function is based on Approximate match. See this example

Book1
	D	E	F	G	H
14	Two	9		Two	2
15				Four	4
16				Three	3
17				Five	5
18				One	1
19				Six	6
20				Eight	8
21				Seven	7
22				Nine	9
Sheet1

Cell Formulas
Range	Formula
E14	E14	=LOOKUP(D14,G14:G22,H14:H22)

It is returning 9 for TWO

 

[CA_Punit]

However i missed the {1;2} this make the formula error free.
Anyway please ensure that $A$7:$A$8 and {1;2} are related.

See this

Book1
	A	B	C	D
1				TOTAL
2	NAME1	EIGHT		21
3	NAME2	SIX	one	7
4	NAME1		SIX	21
5	NAME3	EIGHT	EIGHT	16
6	Name1	Four	Three	21
7	Name 5	Two	Nine	11
8	Name6	Three	Seven	10
9
10
11
12
13	EIGHT	8
14	SIX	6
15	One	1
16	Five	5
17	Two	2
18	Three	3
19	Nine	9
20	Four	4
21	Seven	7
22
Sheet3

Cell Formulas
Range	Formula
D2:D8	D2	=SUM(IFERROR(LOOKUP(($A$2:$A$8=A2)*IFERROR(MATCH($B$2:$C$8,$A$13:$A$21,0),0),{1;2;3;4;5;6;7;8;9},$B$13:$B$21),0))

 

[Mux99]

Book1
	A	B	C	D	E
1				HELPER	TOTAL
2	NAME1	EIGHT		8	14
3	NAME2	SIX	SIX	12	12
4	NAME1		SIX	6	14
5	NAME3	EIGHT	EIGHT	16	16
6
7	EIGHT	8
8	SIX	6
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=SUM(IFERROR(INDEX($B$7:$B$8,N(IF(1,MATCH(B2:C2,$A$7:$A$8,0)))),0))
E2:E5	E2	=SUMIF($A$2:$A$5,A2,$D$2:$D$5)
Press CTRL+SHIFT+ENTER to enter array formulas.

I ended up using the formula above with a helper column as it seems faster than Vlookup/Lookup formulas. Is there an easy way to join these two formulas together to eliminate the helper column?

Thanks to both for your help.