Summing Columns Based on Multiple Criteria

P

Pineapple_Crazy

Board Regular
Joined
May 2, 2017
Messages
51
Hi All,

I've been trying to develop some VBA that searches for a particular employee's name (garnered from another list I have) and sums the hours worked (minus anything associated with "lunch"). Basically this is a huge spreadsheet with many employees names listed at the very top of their hours worked. It is exported from a system that does not give a pretty output (lots of merging and other oddities).

I need to sum the hours in the "Reg", "OT", and "Other" columns that are not associated with "LUNCH" shown in the "Pay" column. I have pasted an example below. Basically for "John Smith" the summed hours should equate to 88.08 hours, minus the hour for lunch. Then for employee "Jimmy James" it should be 40.15 hours (minus the 3 hours for lunch). Please assume that the first column of "Company" starts in Column A. Any suggestions on how to tackle this one would be much appreciated, I haven't had much luck. Thanks!

Lun/BrkStopRegOTOtherPay
CompanyDivisionBranchDepartmentRatesDayStart Date & TimeMinutesTimeErrHoursHoursHours
00000628 - SMITH, JOHNHourly Rate:0
S0471071050100001 None42705.35412/1/201612/1/20161/30/19001/0/190042705.5614.97REG
S0471071050100001 None42705.58212/1/201612/1/20161/0/19001/0/190042705.7083.03REG
S0471071050100001 None42705.70812/1/201612/1/20161/0/19001/0/190042705.7120.08DOVT
S0471071050100001 None42706.35412/2/201612/2/20161/30/19001/0/190042706.5635REG
S0471071050100001 None42706.58312/2/201612/2/20161/0/19001/0/190042706.7083REG
S0471071050100001 None42707.3612/3/201612/3/20161/30/19001/0/190042707.564.8REG
S0471071050100001 None42707.58112/3/201612/3/20161/0/19001/0/190042707.7093.07REG
S0471071050100001 None42707.70912/3/201612/3/20161/0/19001/0/190042707.7150.13OVT
S0471071050100001 None4271012/6/2016OH Record1LUNCH
S0471071050100001 None42710.35512/6/201612/6/20161/30/19001/0/190042710.5645.02REG
S0471071050100001 None42710.58512/6/201612/6/20161/0/19001/0/190042710.7092.98REG
S0471071050100001 None42711.35512/7/201612/7/20161/30/19001/0/190042711.5624.97REG
S0471071050100001 None42711.58312/7/201612/7/20161/0/19001/0/190042711.7093.03REG
S0471071050100001 None42712.35312/8/201612/8/20161/31/19001/0/190042712.5574.88REG
S0471071050100001 None42712.57812/8/201612/8/20161/0/19001/0/190042712.7083.1REG
S0471071050100001 None4271312/9/2016OH Record8PTOH
S0471071050100001 None42714.35812/10/201612/10/20161/30/19001/0/190042714.5584.82REG
S0471071050100001 None42714.57912/10/201612/10/20161/0/19001/0/190042714.7123.18REG
S0471071050100001 None42717.35512/13/201612/13/20161/30/19001/0/190042717.5524.73REG
S0471071050100001 None42717.57312/13/201612/13/20161/0/19001/0/190042717.7093.27REG
S0471071050100001 None42718.35412/14/201612/14/20161/30/19001/0/190042718.5524.75REG
S0471071050100001 None42718.57312/14/201612/14/20161/0/19001/0/190042718.7083.25REG
S0471071050100001 None42719.36612/15/201612/15/20161/30/19001/0/190042719.5484.37REG
S0471071050100001 None42719.56912/15/201612/15/20161/0/19001/0/190042719.723.63REG
S0471071050100001 None42719.7242719.7242719.720042719.7210.02DOVT
Employee Total89.08=79.850.239
00001310 - JAMES, JIMMYHourly Rate:0
S0471071050105133 None4270642706OH Record1LUNCH
S0471071050105133 None42706.35342706.35342706.35331042706.5635.02REG
S0471071050105133 None42706.58442706.58442706.5840042706.7082.98REG
S0471071050105133 None42706.70842706.70842706.7080042706.7090.02DOVT
S0471071050105133 None4270742707OH Record1LUNCH
S0471071050105133 None42707.35342707.35342707.35328042707.5635.03REG
S0471071050105133 None42707.58342707.58342707.5830042707.7062.97REG
S0471071050105133 None42707.70642707.70642707.7060042707.7080.05DOVT
S0471071050105133 None42708.37642708.37642708.37630042708.564.42REG
S0471071050105133 None42708.58142708.58142708.5810042708.7263.5REG
S0471071050105133 None42708.72642708.72642708.7260042708.730.08OVT
S0471071050105133 None42708.7342708.7342708.730042708.7310.03OVT
00001310 - JAMES, JIMMYHourly Rate:0
S0471071050105133 None42709.35442709.35442709.35430042709.5594.92REG
S0471071050105133 None42709.5842709.5842709.580042709.7083.08REG
S0471071050105133 None4271642716OH Record1LUNCH
S0471071050105133 None42716.35142716.35142716.35128042716.5625.05REG
S0471071050105133 None42716.58142716.58142716.5810042716.7042.95REG
S0471071050105133 None42716.70442716.70442716.7040042716.7060.05DOVT
Employee Total43.15=39.920.233

<colgroup><col width="64" style="width:48pt" span="28"> </colgroup><tbody>
</tbody>
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
You must log in or register to reply here.

Similar threads

S
  • Question
Need help creating a multiple tab invoice from a raw data file
Replies
1
Views
202
sreed39
S
J
  • Question
Survey Data analysis to get top 5 skills to improve based on multiple criteria
Replies
4
Views
328
jb00976
J
P
  • Solved
Calculate based on date sum of hours based on date
2
Replies
15
Views
356
psycoperl
P
k3yn0t3
  • Question
Formula to Sum Based on Criteria
2
Replies
19
Views
694
k3yn0t3
k3yn0t3
O
  • Solved
Lookup Multiple Criteria Within Date Range
Replies
3
Views
547
Fluff
Fluff

Forum statistics

Threads
1,214,650
Messages
6,120,736
Members
448,988
Latest member
BB_Unlv

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