Calculate Based on Different Values in Multiple Cells

[Baydoun]

Hi,
i need a calculation in J2 formula which looks at the age of the employee in Column B and compare it with the Age of column F .when it finds matches in age then it Calculate in this way G2*C2 (8.31*2) and H2*D2(10.39*8) and I2*E2(11.22*2) in this case

thanks

A​ Employee	B​ Age	C ​ (Hours Worked) Ordinary Hours	D ​ (Hours Worked) Saturday	E ​ (Hours Worked) Sunday		F​ Rates by Age	G ​ Ordinary Hours	H​ Saturday	I ​ Sunday	J
2 A	20	2	8	2		<16	8.31	10.39	11.22
3 B	16	1	5	1		16	10.39	12.99	14.03
4 C	15	5	2	3		17	12.47	15.59	16.83
5 D	18	8	2	5		18	14.55	12	19.64
6 E	17	6	22	5		19	16.63	20.79	22.45
7 F	19	11	19	8		20	18.71	23.39	25.26
8 G	14	15	18	7

<tbody>
</tbody>

 

[Eric W]

Maybe:

	A	B	C	D	E	F	G	H	I	J	K
1		Age	(Hours Worked) Ordinary Hours	(Hours Worked) Saturday	(Hours Worked) Sunday	Rates by Age	Ordinary Hours	Saturday	Sunday
2	A	20	2	8	2	0	8.31	10.39	11.22	275.06
3	B	16	1	5	1	16	10.39	12.99	14.03	89.37
4	C	15	5	2	3	17	12.47	15.59	16.83	95.99
5	D	18	8	2	5	18	14.55	12	19.64	238.6
6	E	17	6	22	5	19	16.63	20.79	22.45	501.95
7	F	19	11	19	8	20	18.71	23.39	25.26	757.54
8	G	14	15	18	7					390.21

<tbody>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula J2 =SUMPRODUCT(C2:E2,INDEX($G$2:$I$7,MATCH(B2,$F$2:$F$7),0)) <tbody> </tbody>

<tbody>
</tbody>

I'd recommend moving the Age/Rate table somewhere else and putting the total next to column E though. Also note I changed F2 to 0, which will have the effect of <16.

 

[Baydoun]

Waw, you saved me a lot of time with this formula, thank you sooo much

 

[Baydoun]

is it possible to add one more column with HOLIDAY Rate ?

 

[Eric W]

Very easily:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Age	(Hours Worked) Ordinary Hours	(Hours Worked) Saturday	(Hours Worked) Sunday	(Hours Worked) Holiday	Total $			Rates by Age	Ordinary Hours	Saturday	Sunday	Holiday
2	A	20	2	8	2	0	275.06			0	8.31	10.39	11.22	12.21
3	B	16	1	5	1	0	89.37			16	10.39	12.99	14.03	15.01
4	C	15	5	2	3	0	95.99			17	12.47	15.59	16.83	17
5	D	18	8	2	5	1	258.71			18	14.55	12	19.64	20.11
6	E	17	6	22	5	4	569.95			19	16.63	20.79	22.45	24.11
7	F	19	11	19	8	2	805.76			20	18.71	23.39	25.26	27.72
8	G	14	15	18	7	0	390.21

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
<td style="text-align: right;;"
></td>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula G2 =SUMPRODUCT(C2:F2,INDEX($K$2:$N$7,MATCH(B2,$J$2:$J$7),0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Glad I could help!