Calculate Total Hours With Ctiteria

[kumara_faith]

Hi,

I have the following tables:

Table 1			Table 2			Table 3
Staff	Work		Work	Hours		Staff	Hours
Staff1	FD		FD	450		Staff1	1125
Staff2	AL_AM		AL_AM	225		Staff2	450
Staff3	AL_PM		AL_PM	225		Staff3	450
Staff4				0		Staff4	0
Staff1	FD
Staff2	AL_AM
Staff3	AL_PM
Staff4
Staff1	AL_PM
Staff2	AL_AM
Staff3	AL_PM
Staff4

Table 1 is the data. Table 2 indicates the hours for each work type. In Table 3, I am trying to summarize with a formula on the total hours for staff1, staff2 and staff3. The actual result is in Table 3. Is there a formula that I could use in Table 3 ? Appreciate all the help. Thanks.

 

[kumara_faith]

Hi,

Updated the table as below:

	Table 1			Table 2			Table 3	Jan	Feb	Mar
Jan	Staff	Work		Work	Hours		Staff	Hours	Hours	Hours
Jan	Staff1	FD		FD	450		Staff1	450	225	225
Jan	Staff2	AL_AM		AL_AM	225		Staff2	225	225	225
Jan	Staff3	AL_PM		AL_PM	225		Staff3	225	225	225
Jan	Staff4				0		Staff4	0	0	0
Feb	Staff1	FD
Feb	Staff2	AL_AM
Feb	Staff3	AL_PM
Feb	Staff4
March	Staff1	AL_PM
March	Staff2	AL_AM
March	Staff3	AL_PM
March	Staff4

 

[HongRu]

Try.

Table3's Mar should be replaced by March.

Staff month.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Month	Staff	Work		Work	Hours		Staff	Jan	Feb	March
2	Jan	Staff1	FD		FD	450		Staff1	450	450	225
3	Jan	Staff2	AL_AM		AL_AM	225		Staff2	225	225	225
4	Jan	Staff3	AL_PM		AL_PM	225		Staff3	225	225	225
5	Jan	Staff4						Staff4	0	0	0
6	Feb	Staff1	FD
7	Feb	Staff2	AL_AM
8	Feb	Staff3	AL_PM
9	Feb	Staff4
10	March	Staff1	AL_PM
11	March	Staff2	AL_AM
12	March	Staff3	AL_PM
13	March	Staff4
sheet2

Cell Formulas
Range	Formula
I2:K5	I2	=IFERROR(INDEX($F$2:$F$4,MATCH(INDEX($C$2:$C$13,MATCH($H2&I$1,$B$2:$B$13&$A$2:$A$13,0)),$E$2:$E$4,0)),0)
Press CTRL+SHIFT+ENTER to enter array formulas.