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]
 
Sort by date Sort by votes
DanteAmor said:
Try this



ABCDEFGH
Doe, John
Sun 03/31/2019REGULAR-Regular XXXX
Mon 04/01/2019REGULAR-Regular XXXX
Tue 04/02/2019OVERTIME-Overtime XXXX
Wed 04/03/2019REGULAR-Regular XXXX
Thu 04/04/2019OVERTIME-Overtime XXXX
Fri 04/05/2019REGULAR-Regular XXXX
Mon 04/01/2019REGULAR-Regular XXXX
Tue 04/02/2019REGULAR-Regular XXXX
Wed 04/03/2019XXXX
Thu 04/04/2019REGULAR-Regular XXXX
Fri 04/05/2019XXXX
Fri 04/05/2019REGULAR-Regular XXXX

<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><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]Pay Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]Pay Code[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]Hours[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]Dollars[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]OVERTIME-Overtime[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="bgcolor: #92d050"]Doe[/TD]
[TD="bgcolor: #92d050"]David[/TD]
[TD="bgcolor: #92d050"]YG6001144[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: right"]11.14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00b0f0]#00b0f0[/URL] "]Subtotal[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00b0f0]#00b0f0[/URL] , align: right"]36.62[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00b0f0]#00b0f0[/URL] "]XXXX[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="bgcolor: #92d050"]Doe[/TD]
[TD="bgcolor: #92d050"]John[/TD]
[TD="bgcolor: #92d050"]YG6001457[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] "]OVERTIME-Overtime[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: right"]8.37[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] "]OVERTIME-Overtime[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: right"]2.77[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00b0f0]#00b0f0[/URL] "]Subtotal[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00b0f0]#00b0f0[/URL] , align: right"]40.77[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00b0f0]#00b0f0[/URL] "]XXXX[/TD]

</tbody>

CellFormula
H2{=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)))}

<tbody>
</tbody>

<tbody>
</tbody>
Click to expand...

That's exactly what he does in my example:confused:
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
It does but it also populates the answer from the first cell in any following cells with a blank cell reference.
 
Upvote 0
Then you could give the example of the result that the formula puts, even better, you could upload your file and show me with examples.

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
 
Upvote 0
Here is an example all the cells below Sarah have no name so it just pulls the same answer for whatever name is in the first cell in this case Doe, John below is the formula I am using

=IFERROR(SUMIF(OFFSET($F$1,MATCH(SUBSTITUTE(T2,", ",""),$A:$A&$F:$F,0)-1,0,MATCH("Subtotal",OFFSET($K$1,MATCH(SUBSTITUTE(T2,", ",""),$A:$A&$F:$F,0),0,17),0)),$U$1,OFFSET($O$1,MATCH(SUBSTITUTE(T2,", ",""),$A:$A&$F:$F,0)-1,0,MATCH("Subtotal",OFFSET($K$1,MATCH(SUBSTITUTE(T2,", ",""),$A:$A&$F:$F,0),0,17),0))),"")


[TABLE="width: 1131"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 8"] Employee Daily Totals Report[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Support[/TD]
[TD]OVERTIME-Overtime[/TD]
[/TR]
[TR]
[TD="colspan: 15"]Date Range: 03/31/2019 - 04/06/2019 and Employee Effective As Of : 04/09/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Doe, John[/TD]
[TD="align: right"]0.77[/TD]
[/TR]
[TR]
[TD="colspan: 6"]Company Code:[/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]Brown, Sarah[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Last Name[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]First Name[/TD]
[TD="colspan: 2"]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="align: right"]0.77[/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] [/TD]
[TD="align: right"]0.77[/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="align: right"]0.77[/TD]
[/TR]
[TR]
[TD]Doe[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]John[/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="align: right"]0.77[/TD]
[/TR]
[TR]
[TD="align: center"]##########[/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.48[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]0.77[/TD]
[/TR]
[TR]
[TD="align: center"]##########[/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.47[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]0.77[/TD]
[/TR]
[TR]
[TD="align: center"]##########[/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.82[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]0.77[/TD]
[/TR]
[TR]
[TD="align: center"]##########[/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.77[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]0.77[/TD]
[/TR]
[TR]
[TD="align: center"]##########[/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.10[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]0.77[/TD]
[/TR]
[TR]
[TD="align: center"]##########[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 2"]LWOP-Leave Without Pay[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]0.77[/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]46.99[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]0.77[/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]Total[/TD]
[TD="align: right"]9.24[/TD]
[/TR]
[TR]
[TD]Brown[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]Sarah[/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="align: center"]##########[/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]0.27[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD] [/TD]
[TD]OVERTIME-Overtime[/TD]
[/TR]
[TR]
[TD="align: center"]##########[/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.37[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]0.77[/TD]
[/TR]
[TR]
[TD="align: center"]##########[/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.28[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]0.77[/TD]
[/TR]
[TR]
[TD="align: center"]##########[/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]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]0.77[/TD]
[/TR]
[TR]
[TD="align: center"]##########[/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]8.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]0.77[/TD]
[/TR]
[TR]
[TD="align: center"]##########[/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.87[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]0.77[/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]36.62[/TD]
[TD][/TD]
[TD][/TD]
[TD]XXXX[/TD]
[TD][/TD]
[TD]Total[/TD]
[TD="align: right"]3.85[/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]
</tbody>[/TABLE]
 
Upvote 0
But you modified the formula, that's not the formula that I gave you.

=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)))

Change 17 by the number of last row.

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
i had to adjust the column for the sheet I am using and I did a CTRL SHIFT ENTER to get the {} around it in the sheet. It pulls the correct info if i have a name in the reference cell but if that cell is empty to allow for adding names later it pulls the data for the first name on the list.
 
Upvote 0
But the idea is to put a name.
If the cell is blank then it should return blank?

Try:

=IF(G2="","",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))))
 
Upvote 0
That worked like a champ thank you for all your help you are awesome!!
 
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,822
Messages
6,181,165
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