Calculate total pay for employee who may be assigned multiple shifts/tasks in one calendar day (data in 2 different tables) - is IF my only option?

[SAB114]

Hi There,

I need to calculate the total payable to each employee who may be assigned multiple shifts (tasks) on one calendar day.

EX: For 'Helen' in cell B17, I need to reference all shifts she is assigned in the above table (in the B-column) for Aug 23rd, and then sum the total owed to her, cross referencing the shift name in Column A with the amount paid for that shift in the GUIDE RUN Table (Column G).

The only way I can think is to do a whole bunch of IF calculations to add up all the shifts in each day for each guide (=(IF(B4=B17, g4,0))+(IF(B5=B17, g5,0))+(IF(B6=B17, g6,0))...)

Does anyone know a different formula or an easier way to do this?

The example is small but I have about 22 employee to build calculations for with over 15 different assignments (each person would only have 1-3 assignments per day, but they can vary each day).

Any help you can provide is greatly appreciated. Thanks!

 

[Domenic]

Try the following...

B14, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=SUM(SUMIF($F$4:$F$12,IF(INDEX($B$4:$D$12,0,MATCH(B$16,$B$3:$D$3,0))=$A17,$A$4:$A$12),$G$4:$G$12))

Hope this helps!

 

[JamesCanale]

Array formulas for 2013. This should work, but I haven't run 2013 in a long time.

MrExcelPlayground19.xlsx
	A	B	C	D	E	F	G
3	Role	23-Aug	25-Aug	27-Aug
4	A	Helen	Mike	Shelley		A	69
5	B	Mike	Same	Diane		B	69
6	C	Diane	Diane	Helen		C	115
7	D	Helen	Helen	Sam		D	218.5
8	E	Sam	Mike	Darlene		E	223.5
9	F	Anna	Anna	Helen		F	218.5
10	G	Mike	Darlene	Mike		G	223.5
11	H	Diane	Bob	Anna		H	253
12	I	Darlene	Shelley	Bob		I	258
13
14
15		23-Aug	25-Aug	27-Aug
16	Helen	287.5	218.5	333.5
17	Mike	292.5	292.5	223.5
18	Diane	368	115	69
19	Shelley	0	258	69
20	Sam	223.5	0	218.5
21	Anna	218.5	218.5	253
22	Bob	0	253	258
23	Darlene	258	223.5	223.5
Sheet25

Cell Formulas
Range	Formula
B16:D23	B16	=SUMPRODUCT(VLOOKUP($A$4:$A$12,$F$4:$G$12,2,FALSE),--(B$4:B$12=$A16))