Formula or VBA Code to Sum up Columns Based on Criteria from other Columns

[MrClueless]

I currently have a macro that is doing exactly what I need it to do. However I am still doing some Manual work to gather the information I need. I wanted to see if its possible to have a formula sum up the Column: Regular, Overtime and Doubletime hours until it reaches 173 hours. Once it reaches this limit it would sum up corresponding hours in Columns: Regular Rate, Overtime Rate, and Doubletime rate based on the corresponding hours.

Ill use the pasted information below as an example of the manual work I am doing. What I am currently doing is, if in a Month the hours exceed the 173 hour limit ill deduct 173 from the overage amount, in this case 176.25 and multiply the difference of 4.25 by the rate it corresponds to, whether it be regular rate, overtime rate, or doubletime rate. After Ill sum up the columns in Regular Rate, Doubletime Rate, and Overtime Rate based on the Cumulative hours in Column labeled Cumulative which is based on the limit of 173. Im not sure if this is possible and wanted to see if anyone would be able to help.


The following is a sample of what my End Product is

[TABLE="width: 1210"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Day of the Week[/TD]
[TD]Month[/TD]
[TD]Year[/TD]
[TD]Amount[/TD]
[TD]Regular[/TD]
[TD]Overtime[/TD]
[TD]Regular Rate[/TD]
[TD]Overtime Rate[/TD]
[TD]Change[/TD]
[TD]Cumulative[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1210"]
<colgroup><col span="11"></colgroup><tbody>[TR]
[TD][TABLE="width: 1210"]
<colgroup><col span="11"></colgroup><tbody>[TR]
[TD]07-02-2012[/TD]
[TD]Monday[/TD]
[TD]July[/TD]
[TD]2012[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]0[/TD]
[TD]2.88[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]07-03-2012[/TD]
[TD]Tuesday[/TD]
[TD]July[/TD]
[TD]2012[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]0[/TD]
[TD]2.88[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]07-05-2012[/TD]
[TD]Thursday[/TD]
[TD]July[/TD]
[TD]2012[/TD]
[TD]8.5[/TD]
[TD]8.5[/TD]
[TD]0[/TD]
[TD]2.72[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]26.5[/TD]
[/TR]
[TR]
[TD]07-06-2012[/TD]
[TD]Friday[/TD]
[TD]July[/TD]
[TD]2012[/TD]
[TD]8.5[/TD]
[TD]8.5[/TD]
[TD]0[/TD]
[TD]2.72[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]07-07-2012[/TD]
[TD]Saturday[/TD]
[TD]July[/TD]
[TD]2012[/TD]
[TD]6.5[/TD]
[TD]6.5[/TD]
[TD]0[/TD]
[TD]2.08[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]41.5[/TD]
[/TR]
[TR]
[TD]07-09-2012[/TD]
[TD]Monday[/TD]
[TD]July[/TD]
[TD]2012[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]0[/TD]
[TD]2.88[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]50.5[/TD]
[/TR]
[TR]
[TD]07-10-2012[/TD]
[TD]Tuesday[/TD]
[TD]July[/TD]
[TD]2012[/TD]
[TD]7.5[/TD]
[TD]7.5[/TD]
[TD]0[/TD]
[TD]2.4[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]58[/TD]
[/TR]
[TR]
[TD]07-11-2012[/TD]
[TD]Wednesday[/TD]
[TD]July[/TD]
[TD]2012[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]0[/TD]
[TD]2.88[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]07-12-2012[/TD]
[TD]Thursday[/TD]
[TD]July[/TD]
[TD]2012[/TD]
[TD]7.5[/TD]
[TD]7.5[/TD]
[TD]0[/TD]
[TD]2.4[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]74.5[/TD]
[/TR]
[TR]
[TD]07-13-2012[/TD]
[TD]Friday[/TD]
[TD]July[/TD]
[TD]2012[/TD]
[TD]7.5[/TD]
[TD]7.5[/TD]
[TD]0[/TD]
[TD]2.4[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]82[/TD]
[/TR]
[TR]
[TD]07-14-2012[/TD]
[TD]Saturday[/TD]
[TD]July[/TD]
[TD]2012[/TD]
[TD]4.5[/TD]
[TD]4.5[/TD]
[TD]0[/TD]
[TD]1.44[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]86.5[/TD]
[/TR]
[TR]
[TD]07-16-2012[/TD]
[TD]Monday[/TD]
[TD]July[/TD]
[TD]2012[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD]2.24[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]93.5[/TD]
[/TR]
[TR]
[TD]07-17-2012[/TD]
[TD]Tuesday[/TD]
[TD]July[/TD]
[TD]2012[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]0[/TD]
[TD]2.88[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]102.5[/TD]
[/TR]
[TR]
[TD]07-18-2012[/TD]
[TD]Wednesday[/TD]
[TD]July[/TD]
[TD]2012[/TD]
[TD]7.5[/TD]
[TD]7.5[/TD]
[TD]0[/TD]
[TD]2.4[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]110[/TD]
[/TR]
[TR]
[TD]07-19-2012[/TD]
[TD]Thursday[/TD]
[TD]July[/TD]
[TD]2012[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]2.56[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]118[/TD]
[/TR]
[TR]
[TD]07-20-2012[/TD]
[TD]Friday[/TD]
[TD]July[/TD]
[TD]2012[/TD]
[TD]7.5[/TD]
[TD]7.5[/TD]
[TD]0[/TD]
[TD]2.4[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]125.5[/TD]
[/TR]
[TR]
[TD]07-23-2012[/TD]
[TD]Monday[/TD]
[TD]July[/TD]
[TD]2012[/TD]
[TD]7.5[/TD]
[TD]7.5[/TD]
[TD]0[/TD]
[TD]2.4[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]133[/TD]
[/TR]
[TR]
[TD]07-24-2012[/TD]
[TD]Tuesday[/TD]
[TD]July[/TD]
[TD]2012[/TD]
[TD]7.25[/TD]
[TD]7.25[/TD]
[TD]0[/TD]
[TD]2.32[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]140.25[/TD]
[/TR]
[TR]
[TD]07-25-2012[/TD]
[TD]Wednesday[/TD]
[TD]July[/TD]
[TD]2012[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]1.92[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]146.25[/TD]
[/TR]
[TR]
[TD]07-26-2012[/TD]
[TD]Thursday[/TD]
[TD]July[/TD]
[TD]2012[/TD]
[TD]7.5[/TD]
[TD]7.5[/TD]
[TD]0[/TD]
[TD]2.4[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]153.75[/TD]
[/TR]
[TR]
[TD]07-27-2012[/TD]
[TD]Friday[/TD]
[TD]July[/TD]
[TD]2012[/TD]
[TD]7.5[/TD]
[TD]7.5[/TD]
[TD]0[/TD]
[TD]2.4[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]161.25[/TD]
[/TR]
[TR]
[TD]07-30-2012[/TD]
[TD]Monday[/TD]
[TD]July[/TD]
[TD]2012[/TD]
[TD]7.5[/TD]
[TD]7.5[/TD]
[TD]0[/TD]
[TD]2.4[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]168.75[/TD]
[/TR]
[TR]
[TD]07-31-2012[/TD]
[TD]Tuesday[/TD]
[TD]July[/TD]
[TD]2012[/TD]
[TD]7.5[/TD]
[TD]7.5[/TD]
[TD]0[/TD]
[TD]2.4[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]176.25[/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]
[/TR]
</tbody>[/TABLE]