Counting Issues!

[Mrupe86]

Hello,
I am struggling with the creation of the scheduling table. Unfortunate for me, my company does not have a team to help build spreadsheets. I have a schedule, which uses a code, to signify where the employee will work for the day. The code has a key that matches the job duty and hours. I would like to count each occurrence of the key code as the corresponding shift hour, so that I may sum it as hours worked per week. These are also two separate tables. Table 5 (schedule) and Table 7 (Key)

I can use [=IFERROR(XLOOKUP([@[Exclusive Position Control/Relief]],Table7[Schedule Code:],Table7[Shift Hours]),0)*5] , but when an the relief employee works T1, G1 and H4 I can't get them to add together.
Any suggestions would be greatly appreciated!

 

[Cubist]

We can't manipulate and test with images. Please post your data in a copy-able format. Mr.Excel has XL2BB tool which makes it easy to exchange snipets of your worksheet.

Example:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Component Name	Replacement Interval	Component Cost
2	Component 1	2000	$ 3,000
3	Component 2	3000	$ 7,000
4	Component 3	3000	$ 8,500
5	Component 4	2500	$ 4,000
6
7		January	February	March	April	May	June	July	August	September	October	November	December
8	Accumulated Equipment Hours	2,543	2,876	3,351	3,820	4,352	4,762	5,273	5,694	6,150	6,618	7,237	7,651
9	Cost	$ 7,000	$ -	$ 15,500	$ -	$ 3,000	$ -	$ 4,000	$ -	$ 18,500	$ -	$ -	$ 4,000
Sheet4

Cell Formulas
Range	Formula
B9:M9	B9	=SUMPRODUCT(QUOTIENT(B8,+$B$2:$B$5),$C$2:$C$5)-SUM($A$9:A9)

 

[Mrupe86]

Position Control TEMPLATE.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
3	Total Scheduled Productive Hours Wk 1	Total Scheduled Productive Hours Wk 2	Total Scheduled Productive Hours	Name2	1/12	1/1	1/2	4/7	4/8	4/9	4/10	Column1	4/11	4/12	4/13	4/14	4/15	4/16	4/17		Position:	Counted on Schedule	Schedule Code:	Shift Hours
4	40		40	Jane Doe	T1	X	T1	T1	X	T1	T1		T1	T1	X	X	T1	T1	T1		Tray	14	T1	8
5	40		40	John Doe	T2	T2	X	X	T2	T2	T2		T2	X	T2	T2	X	T2	T2		Tray	12	T2	8
6	0		0	Chris Pratt	X	T1	G1	G1	T1	X	X		X	T2	T1	T1	T2	X	X		Host	0	H12	8
7	40		40	Chris Hanson	P1	P1	P1	P1	P1				P1	P1	P1	P1	P1				Host	4	H2	8
8	20		20	Troy Psy	E1	E1	H2	H2	X	X	P1		E1	E1	H2	H2	X	X	P1		Dish	2	G1	4
9	20		20	Ali Frank																	Dish	0	G2	4
10	40		40	Vacant																	Cook	4	E1	8
11	40		40	Vacant																	Cook	0	E2	8
12	40		40	Jennifer Freeze																	Host	0	H3	8
13	25		25	Vacant																	Host	0	H4	5
14	25		25	Vacant																	Production	12	P1	5
15	25		25	Vacant																	Production	0	P2	5
16	0		0																		Day Off	18	X	0
Master Scheduler

Cell Formulas
Range	Formula
D4:D16	D4	=IFERROR(XLOOKUP([@[Exclusive Position Control/Relief]],Table7[Schedule Code:],Table7[Shift Hours]),0)*5
F4:F16	F4	=[@[Total Scheduled Productive Hours Wk 1]]+[@[Total Scheduled Productive Hours Wk 2]]
Y4:Y16	Y4	=COUNTIF(Table5[[1/12]:[4/17]],[@[Schedule Code:]])

Named Ranges
Name	Refers To	Cells
ScheduleCodes	=Table7[Schedule Code:]	Y4, D4:D16

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:C54,E4:V54	Cell Value	contains "X"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
H4:N16	List	=ScheduleCodes
P4:V16	List	=ScheduleCodes

 

[Cubist]

Thanks for the XL2BB. See if this works for you:

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1		Total Scheduled Productive Hours Wk 1	Total Scheduled Productive Hours Wk 2	Total Scheduled Productive Hours	Name2	12-Jan	1-Jan	2-Jan	7-Apr	8-Apr	9-Apr	10-Apr	Column1	11-Apr	12-Apr	13-Apr	14-Apr	15-Apr	16-Apr	17-Apr		Position:	Counted on Schedule	Schedule Code:	Shift Hours
2	T1	40	40	80	Jane Doe	T1	X	T1	T1	X	T1	T1		T1	T1	X	X	T1	T1	T1		Tray	14	T1	8
3	T2	40	40	80	John Doe	T2	T2	X	X	T2	T2	T2		T2	X	T2	T2	X	T2	T2		Tray	12	T2	8
4	Relief	24	32	56	Chris Pratt	X	T1	G1	G1	T1	X	X		X	T2	T1	T1	T2	X	X		Host	0	H12	8
5	H2	25	25	50	Chris Hanson	P1	P1	P1	P1	P1				P1	P1	P1	P1	P1				Host	0	H2	8
6																						Dish	2	G1	4
7																						Dish	0	G2	4
8																						Cook	0	E1	8
9																						Cook	0	E2	8
10																						Host	0	H3	8
11																						Host	0	H4	5
12																						Production	10	P1	5
13																						Production	0	P2	5
14																						Day Off	14	X	0
Sheet9

Cell Formulas
Range	Formula
D2:D5	D2	=SUM(COUNTIFS(H2:N2,$Z$2:$Z$14)*($AA$2:$AA$14))
E2:E5	E2	=SUM(COUNTIFS(P2:V2,$Z$2:$Z$14)*($AA$2:$AA$14))
F2:F5	F2	=SUM(D2:E2)
Y2:Y14	Y2	=COUNTIFS(H2:V5,Z2:Z14)
Dynamic array formulas.

 

[Mrupe86]

Thanks for the XL2BB. See if this works for you:

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1		Total Scheduled Productive Hours Wk 1	Total Scheduled Productive Hours Wk 2	Total Scheduled Productive Hours	Name2	12-Jan	1-Jan	2-Jan	7-Apr	8-Apr	9-Apr	10-Apr	Column1	11-Apr	12-Apr	13-Apr	14-Apr	15-Apr	16-Apr	17-Apr		Position:	Counted on Schedule	Schedule Code:	Shift Hours
2	T1	40	40	80	Jane Doe	T1	X	T1	T1	X	T1	T1		T1	T1	X	X	T1	T1	T1		Tray	14	T1	8
3	T2	40	40	80	John Doe	T2	T2	X	X	T2	T2	T2		T2	X	T2	T2	X	T2	T2		Tray	12	T2	8
4	Relief	24	32	56	Chris Pratt	X	T1	G1	G1	T1	X	X		X	T2	T1	T1	T2	X	X		Host	0	H12	8
5	H2	25	25	50	Chris Hanson	P1	P1	P1	P1	P1				P1	P1	P1	P1	P1				Host	0	H2	8
6																						Dish	2	G1	4
7																						Dish	0	G2	4
8																						Cook	0	E1	8
9																						Cook	0	E2	8
10																						Host	0	H3	8
11																						Host	0	H4	5
12																						Production	10	P1	5
13																						Production	0	P2	5
14																						Day Off	14	X	0
Sheet9

Cell Formulas
Range	Formula
D2:D5	D2	=SUM(COUNTIFS(H2:N2,$Z$2:$Z$14)*($AA$2:$AA$14))
E2:E5	E2	=SUM(COUNTIFS(P2:V2,$Z$2:$Z$14)*($AA$2:$AA$14))
F2:F5	F2	=SUM(D2:E2)
Y2:Y14	Y2	=COUNTIFS(H2:V5,Z2:Z14)
Dynamic array formulas.

Thank you very much! I was able to change the formulas to the column header and bam! Thank you so much.

 

[Cubist]

You're welcome.