# Calculate Based on Different Values in Multiple Cells

#### Baydoun

##### Board Regular
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>

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Eric W

##### MrExcel MVP
Maybe:

ABCDEFGHIJK
1Age(Hours Worked) Ordinary Hours(Hours Worked) Saturday(Hours Worked) SundayRates by AgeOrdinary HoursSaturdaySunday
2A2028208.3110.3911.22275.06
3B161511610.3912.9914.0389.37
4C155231712.4715.5916.8395.99
5D188251814.551219.64238.6
6E1762251916.6320.7922.45501.95
7F19111982018.7123.3925.26757.54
8G1415187390.21

<tbody>
</tbody>
Sheet8

Worksheet Formulas
CellFormula
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.

Last edited:

#### Baydoun

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

#### Baydoun

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

#### Eric W

##### MrExcel MVP
Very easily:

ABCDEFGHIJKLMNO
1Age(Hours Worked) Ordinary Hours(Hours Worked) Saturday(Hours Worked) Sunday(Hours Worked) HolidayTotal \$Rates by AgeOrdinary HoursSaturdaySundayHoliday
2A202820275.0608.3110.3911.2212.21
3B16151089.371610.3912.9914.0315.01
4C15523095.991712.4715.5916.8317
5D188251258.711814.551219.6420.11
6E1762254569.951916.6320.7922.4524.11
7F19111982805.762018.7123.3925.2627.72
8G14151870390.21

<td style="text-align: right;;"
></td>
</tbody>
Sheet8

Worksheet Formulas
CellFormula
G2=SUMPRODUCT(C2:F2,INDEX(\$K\$2:\$N\$7,MATCH(B2,\$J\$2:\$J\$7),0))

</tbody>

<tbody>
</tbody>

Replies
10
Views
296
Replies
5
Views
126
Replies
3
Views
66
Replies
3
Views
59
Replies
0
Views
224

1,109,385
Messages
5,528,396
Members
409,817
Latest member
JiNXX9500

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...