Find, maybe

C

chadlaw32

Board Regular
Joined
Jul 29, 2014
Messages
84
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
I need to look up the sup total or Over time based on the name. Example i want to put Doe, John in a cell and have it bring me 0.77, any ideas?


[TABLE="width: 787"]
<colgroup><col><col span="4"><col><col><col span="3"><col><col span="3"><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Pay Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pay Code[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Hours[/TD]
[TD][/TD]
[TD][/TD]
[TD]Dollars[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Doe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jane[/TD]
[TD]YG6001144[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sun 03/31/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]REGULAR-Regular[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.27[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD]Mon 04/01/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]REGULAR-Regular[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7.37[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD]Tue 04/02/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]REGULAR-Regular[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8.28[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD]Wed 04/03/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]REGULAR-Regular[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7.83[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD]Thu 04/04/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]VACATION-VACATION[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD]Fri 04/05/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]REGULAR-Regular[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4.87[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Subtotal[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]36.62[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Doe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]John[/TD]
[TD]YG6001457[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mon 04/01/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]REGULAR-Regular[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7.05[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD]Tue 04/02/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]REGULAR-Regular[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7.65[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD]Wed 04/03/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]REGULAR-Regular[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8.37[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD]Thu 04/04/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]REGULAR-Regular[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9.35[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD]Fri 04/05/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]OVERTIME-Overtime[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.77[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD]Fri 04/05/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]REGULAR-Regular[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7.58[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Subtotal[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]40.77[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Try with this array formula:

<b></b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:115.2px;" /><col style="width:140.51px;" /><col style="width:79.42px;" /><col style="width:47.13px;" /><col style="width:49.75px;" /><col style="width:41.89px;" /><col style="width:90.76px;" /><col style="width:133.53px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; ">Pay Date</td><td style="background-color:#ffff00; ">Pay Code</td><td style="background-color:#ffff00; "> </td><td style="background-color:#ffff00; ">Hours</td><td style="background-color:#ffff00; ">Dollars</td><td > </td><td style="background-color:#ffff00; ">Name</td><td style="background-color:#ffff00; ">OVERTIME-Overtime</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#92d050; ">Doe</td><td style="background-color:#92d050; ">Jane</td><td style="background-color:#92d050; ">YG6001144</td><td > </td><td > </td><td > </td><td >Doe, John</td><td style="text-align:right; ">0.77</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Sun 03/31/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">0.27</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Mon 04/01/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">7.37</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Tue 04/02/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">8.28</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Wed 04/03/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">7.83</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Thu 04/04/2019</td><td >VACATION-VACATION</td><td > </td><td style="text-align:right; ">8</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Fri 04/05/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">4.87</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td style="background-color:#00b0f0; ">Subtotal</td><td style="background-color:#00b0f0; text-align:right; ">36.62</td><td style="background-color:#00b0f0; ">XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#92d050; ">Doe</td><td style="background-color:#92d050; ">John</td><td style="background-color:#92d050; ">YG6001457</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >Mon 04/01/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">7.05</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >Tue 04/02/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">7.65</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >Wed 04/03/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">8.37</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >Thu 04/04/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">9.35</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >Fri 04/05/2019</td><td >OVERTIME-Overtime</td><td > </td><td style="text-align:right; ">0.77</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >Fri 04/05/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">7.58</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td><td > </td><td style="background-color:#00b0f0; ">Subtotal</td><td style="background-color:#00b0f0; text-align:right; ">40.77</td><td style="background-color:#00b0f0; ">XXXX</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formula</td></tr><tr><td >H2</td><td >{=OFFSET(D1,MATCH(SUBSTITUTE(G2,", ",""),A1:A1000&B1:B1000,0)+MATCH(H1,OFFSET(B1,MATCH(SUBSTITUTE(G2,", ",""),A1:A1000&B1:B1000,0),0,MATCH("subtotal",OFFSET(C1,MATCH(SUBSTITUTE(G2,", ",""),A1:A1000&B1:B1000,0),0,1000),0)),0)-1,0)}</td></tr></table></td></tr></table> <br /><br />

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
 
Upvote 0
Solution
That works great thank you very much!! What if they have 2 listings for Over time is there a way it can add the two numbers?

[TABLE="width: 632"]
<colgroup><col><col span="4"><col><col><col span="3"><col><col span="3"><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Sun 03/31/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]REGULAR-Regular[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]7.05[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD]Mon 04/01/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]REGULAR-Regular[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8.60[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD]Tue 04/02/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]REGULAR-Regular[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8.62[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD]Wed 04/03/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]REGULAR-Regular[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]7.90[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD]Thu 04/04/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]OVERTIME-Overtime[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.97[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD]Thu 04/04/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]REGULAR-Regular[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]7.83[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD]Fri 04/05/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]OVERTIME-Overtime[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]7.82[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXXX[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Looking at what I am doing I don't need the subtotal just a total for all lines over Overtime for each person if that helps.
 
Upvote 0
You could put an example with data of what you have and what you expect of result.
 
Upvote 0
Below is the example. It adds together the two or more of the OVERTIME-Overtime numbers under John Doe. I need it to add the overtime numbers because you could have over 40 hours and not be on over time so the subtotal does not work.

[TABLE="width: 632"]
<colgroup><col><col span="4"><col><col><col span="3"><col><col span="3"><col><col span="2"><col></colgroup><tbody>[TR]
[TD][TABLE="width: 979"]
<colgroup><col><col span="4"><col><col><col span="3"><col><col span="3"><col><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD]Last Name[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]First Name[/TD]
[TD]Position ID[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]Doe, John[/TD]
[TD="align: right"]7.05[/TD]
[/TR]
[TR]
[TD]Pay Date[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Pay Code[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Hours[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Dollars[/TD]
[TD][/TD]
[TD]Smith, Frank[/TD]
[TD="align: right"]4.15[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Doe[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]John[/TD]
[TD]YG6001163[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sun 03/31/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]REGULAR-Regular[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mon 04/01/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]REGULAR-Regular[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4.13[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tue 04/02/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]REGULAR-Regular[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]13.92[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wed 04/03/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]REGULAR-Regular[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4.30[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thu 04/04/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]REGULAR-Regular[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8.42[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fri 04/05/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]OVERTIME-Overtime[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4.93[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fri 04/05/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]REGULAR-Regular[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3.23[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sat 04/06/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]OVERTIME-Overtime[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2.12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Subtotal[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]47.05[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Frank[/TD]
[TD]YG6001366[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mon 04/01/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]REGULAR-Regular[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8.50[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tue 04/02/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]REGULAR-Regular[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10.15[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wed 04/03/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]REGULAR-Regular[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8.50[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thu 04/04/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]REGULAR-Regular[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8.50[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fri 04/05/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]OVERTIME-Overtime[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4.15[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fri 04/05/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]REGULAR-Regular[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4.35[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Subtotal[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]44.15[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Try this

<b></b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:101.7px;" /><col style="width:137.82px;" /><col style="width:67.49px;" /><col style="width:39.92px;" /><col style="width:41.82px;" /><col style="width:35.17px;" /><col style="width:76.04px;" /><col style="width:129.27px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; ">Pay Date</td><td style="background-color:#ffff00; ">Pay Code</td><td style="background-color:#ffff00; "> </td><td style="background-color:#ffff00; ">Hours</td><td style="background-color:#ffff00; ">Dollars</td><td > </td><td style="background-color:#ffff00; ">Name</td><td style="background-color:#ffff00; ">OVERTIME-Overtime</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#92d050; ">Doe</td><td style="background-color:#92d050; ">David</td><td style="background-color:#92d050; ">YG6001144</td><td > </td><td > </td><td > </td><td >Doe, John</td><td style="background-color:#ff0000; color:#ffffff; text-align:right; ">11.14</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Sun 03/31/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">0.27</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Mon 04/01/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">7.37</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Tue 04/02/2019</td><td >OVERTIME-Overtime</td><td > </td><td style="text-align:right; ">8.28</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Wed 04/03/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">7.83</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Thu 04/04/2019</td><td >OVERTIME-Overtime</td><td > </td><td style="text-align:right; ">8</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Fri 04/05/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">4.87</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td style="background-color:#00b0f0; ">Subtotal</td><td style="background-color:#00b0f0; text-align:right; ">36.62</td><td style="background-color:#00b0f0; ">XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#92d050; ">Doe</td><td style="background-color:#92d050; ">John</td><td style="background-color:#92d050; ">YG6001457</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >Mon 04/01/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">7.05</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >Tue 04/02/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">7.65</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >Wed 04/03/2019</td><td style="background-color:#ff0000; color:#ffffff; ">OVERTIME-Overtime</td><td style="background-color:#ff0000; color:#ffffff; "> </td><td style="background-color:#ff0000; color:#ffffff; text-align:right; ">8.37</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >Thu 04/04/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">9.35</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >Fri 04/05/2019</td><td style="background-color:#ff0000; color:#ffffff; ">OVERTIME-Overtime</td><td style="background-color:#ff0000; color:#ffffff; "> </td><td style="background-color:#ff0000; color:#ffffff; text-align:right; ">2.77</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >Fri 04/05/2019</td><td >REGULAR-Regular</td><td > </td><td style="text-align:right; ">7.58</td><td >XXXX</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td><td > </td><td style="background-color:#00b0f0; ">Subtotal</td><td style="background-color:#00b0f0; text-align:right; ">40.77</td><td style="background-color:#00b0f0; ">XXXX</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >H2</td><td >{=SUMIF(OFFSET(B1,MATCH(SUBSTITUTE(G2,", ",""),A1:A17&B1:B17,0)-1,0,MATCH("Subtotal",OFFSET(C1,MATCH(SUBSTITUTE(G2,", ",""),A1:A17&B1:B17,0),0,17),0)),H1,OFFSET(D1,MATCH(SUBSTITUTE(G2,", ",""),A1:A17&B1:B17,0)-1,0,MATCH("Subtotal",OFFSET(C1,MATCH(SUBSTITUTE(G2,", ",""),A1:A17&B1:B17,0),0,17),0)))}</td></tr></table></td></tr></table> <br /><br />
 
Upvote 0
Then I did not understand, again, what data do you have and what are you going to put in name and what do you expect of result?
 
Upvote 0
Same data as above but i do not care about subtotals just that i get the total overtime as seen below. John Doe has two line of overtime totaling 9.14 hours.


ABCDEFGH
Pay DatePay CodeHoursDollarsNameOVERTIME-Overtime
Doe, John
Sun 03/31/2019REGULAR-RegularXXXX
Mon 04/01/2019REGULAR-RegularXXXX
Tue 04/02/2019REGULAR-RegularXXXX
Wed 04/03/2019REGULAR-RegularXXXX
Thu 04/04/2019VACATION-VACATIONXXXX
Fri 04/05/2019REGULAR-RegularXXXX
SubtotalXXXX
Mon 04/01/2019REGULAR-RegularXXXX
Tue 04/02/2019REGULAR-RegularXXXX
Wed 04/03/2019OVERTIME-OvertimeXXXX
Thu 04/04/2019REGULAR-RegularXXXX
Fri 04/05/2019OVERTIME-OvertimeXXXX
Fri 04/05/2019REGULAR-RegularXXXX
SubtotalXXXX

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #92D050"]Doe[/TD]
[TD="bgcolor: #92D050"]Jane[/TD]
[TD="bgcolor: #92D050"]YG6001144[/TD]

[TD="align: right"]9.14[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]0.27[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]7.37[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]8.28[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]7.83[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]8[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]4.87[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]36.62[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #92D050"]Doe[/TD]
[TD="bgcolor: #92D050"]John[/TD]
[TD="bgcolor: #92D050"]YG6001457[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]7.05[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]7.65[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]8.37[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]9.35[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]0.77[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]7.58[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]40.77[/TD]

</tbody>
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Improving my formulas/approach for date/time analysis
Replies
9
Views
1K
mediumrare
M
Ironman
  • Solved
Msgbox when any one of a number of subtotals has been exceeded
8 9 10
Replies
92
Views
5K
Michael M
Michael M
L
  • Question Question
number based on the value of another column.
Replies
9
Views
861
Fluff
Fluff
Dreamteam
  • Question Question
Flipping values in excel to column titles
Replies
5
Views
792
MickG
M
Y
  • Question Question
EXCEL- Lookup Function to create a new Table from another table.
Replies
0
Views
522
yunus786
Y

Forum statistics

Threads
1,224,821
Messages
6,181,163
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top