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]

Select the D2 to D5 and Press F2 and Then Press Control+Shift+Enter

 

[Mux99]

Select the D2 to D5 and Press F2 and Then Press Control+Shift+Enter

I tried this and D2 to D5 all equal 14. This is correct for D2, but then D3 to D5 are also referencing ($A$2:$A$5=A2) as they all have the same formula. If I try to edit the formula or insert a row or column, the following error displays: You can't change part of an array.

 

[Fluff]

If you select D3 & use the evaluate button on the formula tab do you see this as the last step

 

[Mux99]

If you select D3 & use the evaluate button on the formula tab do you see this as the last step
View attachment 19032

All steps are listed below:

SUMPRODUCT(($A$2:$A$5=A3)*IFERROR(VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0),0))
SUMPRODUCT(({"NAME1";"NAME2";"NAME1";"NAME3"}="NAME2")*IFERROR(VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0),0))
SUMPRODUCT(({FALSE;TRUE;FALSE;FALSE})*IFERROR(VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0),0))
SUMPRODUCT(({FALSE;TRUE;FALSE;FALSE})*IFERROR(VLOOKUP("EIGHT",{"Eight",8;"Six",6},2,0),0))
SUMPRODUCT(({FALSE;TRUE;FALSE;FALSE})*IFERROR(8,0))
SUMPRODUCT(({FALSE;TRUE;FALSE;FALSE})*8)
SUMPRODUCT(({0;8;0;0})
8

 

[Fluff]

In that case it appears that the formula only works with dynamic aware Excel. Which is 365 with the dynamic array functions.

 

[CA_Punit]

Try only this in VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0) in E2 and Drag down till F5 and post the result using xl2bb

 

[Mux99]

Try only this in VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0) in E2 and Drag down till F5 and post the result using xl2bb

Book1.xlsx
	A	B	C	D	E
1				TOTAL
2	NAME1	EIGHT		16	8
3	NAME2	SIX	SIX	8	8
4	NAME1		SIX	16	8
5	NAME3	EIGHT	EIGHT	8	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))
E2:E5	E2	=VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0)
Press CTRL+SHIFT+ENTER to enter array formulas.

Is there a workaround for this without dynamic array formulas as I would prefer if this would be compatible with older excel versions too.

 

[Fluff]

Why not just put the numbers into the cells, rather than the words?

 

[Mux99]

Why not just put the numbers into the cells, rather than the words?

Conditional formatting is being used depending on the text so I can't just use numbers. All matching text need to be linked to a number, and I need the total sum based on the name in Column A as they are listed multiple times.

 

[Fluff]

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.