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]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
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		38
3	Name2		Warehouse 0600-1400		Warehouse 0600-1400		Warehouse 0600-1400		24		24
4	Name3	Reception 0800-1300	Reception 0800-1300	Reception 0800-1300	Reception 0800-1300	Reception 0800-1300			25		25
5	Name4	Reception 0800-1300	Reception 0800-1300	Reception 0800-1300	Reception 1300-1700	Reception 1300-1700			20		23
6
7
8	Sheet2	A	B
9	1	Warehouse 0600-1400	8
10	2	Warehouse 1400-2200	8
11	3	Warehouse 2200-0600	8
12	4	Extra 0900-1500	6
13	5	Reception 0800-1300	5
14	6	Reception 1300-1700	4
Sheet1

Cell Formulas
Range	Formula
K2:K5	K2	=SUM(IFERROR(VLOOKUP(MATCH(B2:H2,TRANSPOSE($B$9:$B$14),0),$A$9:$C$14,3,0),"FALSE"))

 

[CA_Punit]

Book1
	A	B	C	D	E	F	G	H	I	J	K
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		38
3	Name2		Warehouse 0600-1400		Warehouse 0600-1400		Warehouse 0600-1400		24		24
4	Name3	Reception 0800-1300	Reception 0800-1300	Reception 0800-1300	Reception 0800-1300	Reception 0800-1300			25		25
5	Name4	Reception 0800-1300	Reception 0800-1300	Reception 0800-1300	Reception 1300-1700	Reception 1300-1700			20		23
6
7
8	A	B
9	Warehouse 0600-1400	8
10	Warehouse 1400-2200	8
11	Warehouse 2200-0600	8
12	Extra 0900-1500	6
13	Reception 0800-1300	5
14	Reception 1300-1700	4
Sheet1

Cell Formulas
Range	Formula
K2:K5	K2	=SUM(IFERROR(INDEX($B$9:$B$14,MATCH(B2:H2,TRANSPOSE($A$9:$A$14),0)),FALSE))

I forgot to edit Row No in Sheet 2

 

[Mux99]

I forgot to edit Row No in Sheet 2

Thanks for the help but I tried them both without success.

The Vlookup formula displays #VALUE! error and the second Index formula results "0".

 

[CA_Punit]

Please press ctrl+shift+enter and not just enter after formula

 

[Mux99]

Please press ctrl+shift+enter and not just enter after formula

I did and the first formula shows #VALUE! error and the second index formula results in "8"

 

[Mux99]

[/RANGE]

Cell Formulas
Range	Formula
K2:K5	K2	=SUM(IFERROR(INDEX($B$9:$B$14,MATCH(B2:H2,TRANSPOSE($A$9:$A$14),0)),FALSE))

I replaced "FALSE" with 0 and the formula seems to be working.

Would it be possible to sum the total based on the name in Column A without a helper column. Names will be listed multiple times. Example: EIGHT=8 & SIX=6. Example below

			TOTAL
Name1	EIGHT		14
Name2	SIX	SIX	12
Name1		SIX	14
Name3	EIGHT	EIGHT	16

 

[Mux99]

Cell Formulas
Range	Formula
K2:K5	K2	=SUM(IFERROR(INDEX($B$9:$B$14,MATCH(B2:H2,TRANSPOSE($A$9:$A$14),0)),FALSE))

Is it possible that this formula only works on an older version of excel, as this worked for me earlier but isn't working now for some reason on Office 2019?

 

[CA_Punit]

It should work.

 

[Mux99]

It should work.

I've tried multiple times and for some reason it works on Office 2016, but only counts column B with Office 2019. I did press Ctrl+Shift+Enter when entering the formula.

Is there any chance the below can be done in one formula without a helper column?

Thanks again

Would it be possible to sum the total based on the name in Column A without a helper column. Names will be listed multiple times. Example: EIGHT=8 & SIX=6. Example below

			TOTAL
Name1	EIGHT		14
Name2	SIX	SIX	12
Name1		SIX	14
Name3	EIGHT	EIGHT	16