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]

How did your query data set changed from the OP.

Anyway without helper column it can be calculated however you have to hard code the Criteria with value in the formula itself.

 

[Mux99]

How did your query data set changed from the OP.

Anyway without helper column it can be calculated however you have to hard code the Criteria with value in the formula itself.

That was just an easier example as I would need the formula to work on 2000 rows.

Any help would be appreciated.

 

[Mux99]

It should work.

I can confirm that this only worked with office 365. Tried on different PCs with Office 2019 & 2016 and it did not work on both.

 

[CA_Punit]

Book1
	A	B	C	D	E
1			TOTAL
2	Name1	EIGHT		14	??? How did you get 14 here
3	Name2	SIX	SIX	12	ok
4	Name1		SIX	14	??? How did you get 14 here
5	Name3	EIGHT	EIGHT	16
6
Sheet1

Please reply (What is your expected Result)

 

[Mux99]

Book1
	A	B	C	D	E
1			TOTAL
2	Name1	EIGHT		14	??? How did you get 14 here
3	Name2	SIX	SIX	12	ok
4	Name1		SIX	14	??? How did you get 14 here
5	Name3	EIGHT	EIGHT	16
6
Sheet1

Please reply (What is your expected Result)

Since names in column A row 2 & 4 match, then SUM B2:C2 + B4:C4. Is this possible without a helper column?

 

[CA_Punit]

So i have entered only 2 Lookup values "Eight and Six, If you have more values than you can design a lookup table for automatic updation

Book1
	A	B	C	D
1			TOTAL
2	Name1	EIGHT		14
3	Name2	SIX	SIX	12
4	Name1		SIX	14
5	Name3	EIGHT	EIGHT	16
Sheet1

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

 

[Mux99]

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

Not sure why but my Excel 2016 is driving me crazy. When I tried the following formula, it resulted in D2 & D4 = 16, D3 & D5 = 8, which doesn't make any sense at all.

I then tried the exact same formula in google spreadsheets and it seems to work. Will try again tomorrow on Office 365 but i'm guessing it should work too. Thanks alot

 

[CA_Punit]

Can you post the result using xl2bb

xl2bb is an excel addin available in the right hand side of reply box. Download and unzipp it and run. Then you will get an Mr Excel Addin.
Lets try why office 2016 is resulting in error.
Have you tried to confirm the formula with Ctrl+shift+Enter

 

[Fluff]

With 2016 try confirming the formula in D2 with Ctrl Shift Enter, rather than just Enter & then drag down.

 

[Mux99]

Can you post the result using xl2bb

xl2bb is an excel addin available in the right hand side of reply box. Download and unzipp it and run. Then you will get an Mr Excel Addin.
Lets try why office 2016 is resulting in error.
Have you tried to confirm the formula with Ctrl+shift+Enter

Book1
	A	B	C	D
1				TOTAL
2	NAME1	EIGHT		16
3	NAME2	SIX	SIX	8
4	NAME1		SIX	16
5	NAME3	EIGHT	EIGHT	8
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=SUMPRODUCT(($A$2:$A$5=A2)*IFERROR(VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

I entered the same formula in Office 2016 (with Ctrl+Shift+Enter) and google spreadsheets and got a different result for some reason so I'm not sure exactly what happened.