Calculate Based on Different Values in Multiple Cells

Baydoun

Board Regular
Joined
Apr 6, 2016
Messages
134
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 A20282<168.3110.3911.22
3 B161511610.3912.9914.03
4 C155231712.4715.5916.83
5 D188251814.551219.64
6 E1762251916.6320.7922.45
7 F19111982018.7123.3925.26
8 G1415187

<tbody>
</tbody>
 

Some videos you may like

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
Joined
Aug 18, 2015
Messages
10,234
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
Joined
Apr 6, 2016
Messages
134
Waw, you saved me a lot of time with this formula, thank you sooo much
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,234
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

<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
CellFormula
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! :cool:
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top