adding a 1.5 OT hours to my subtotal

[Bob_ipc]

Hey guys I am trying to find the subtotal of numbers in a column, However whenever the number is greater than 9 I would like it to calculate X1.5
How would I go about getting that?

Name Total Hours

Bob 9
Bob 7
Bob 9
Bob 10 Total including OT?
Bob 9
Bob 15
Bob 8
Bob 12

 

[aaronbens]

The following should work (for the formula the hours were in cells B1:B9):
=SUM(B1:B8)+(SUMIF(B1:B8,">"&9,B1:B8)*0.5)

 

[Bob_ipc]

Hey thanks for the quick response, The formula works great for as a Sum, my list is a filtered list how could I modify so that it will look at the filtered selection only?

 

[Bob_ipc]

Hey this is what I have so far but I just can't get it to work. =SUBTOTAL(9,F13:F640)+sumifs(f13:f640,">"&9,f13:f640)*0.5

 

[aaronbens]

Sorry, I missed the subtotal part of it. Try this:
=SUBTOTAL(9,F13:F640)+(SUMPRODUCT(SUBTOTAL(3,OFFSET(F13:F640,ROW(F13:F640)-MIN(ROW(F13:F640)),,1)),--(F13:F640>9),F13:F640))*0.5

 

[Bob_ipc]

Thanks for that, Im still not getting the result I'm looking for,

I have 243 total hours
145 hours are regular time hours (9 hours or less)
98 hours are OT hours (any hours over 9) so I want to multiply 98 by 1.5 to give me 147 hours

So after I enter the formula my result should be 292 hours and I am getting 349.5

What am I doing wrong?

 

[aaronbens]

Humm, I wish I could see the data set. I have set up 4 different scenarios to test the formula vs manual calculations and on my side they are matching

 

[Bob_ipc]

Ok I hope this helps, Column 1 is A2:A22 and Column 2 is B2:B22, the headings name and hours are my drop down lists, and I would like regular hours and ot hours at the top in totals so that the 98 OT hours become 145 regular hours and the
already existing regular hours (hours 9 and under) are added together which should be 292. Is that sort of a better explanation?


[TABLE="width: 162"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Column1[/TD]
[TD]Column2[/TD]
[/TR]
[TR]
[TD]Total Hours[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]10.5[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]21.5[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD="align: right"]8.5[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD="align: right"]16.5[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Kevin[/TD]
[TD="align: right"]21.5[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Kevin[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD]Kevin[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD="align: right"]8.5[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD="align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]

 

[aaronbens]

If you run the formula on this data set ("Name" is in A3 and "Hours" is in B3) and the data is in A4:B20. What do you get when you filter for Bob?

=SUBTOTAL(9,B4:B20)+(SUMPRODUCT(SUBTOTAL(3,OFFSET(B4:B20,ROW(B4:B20)-MIN(ROW(B4:B20)),,1)),--(B4:B20>9),B4:B20))*0.5

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 64"]Total Hours[/TD]
[TD="class: xl63, width: 64, align: right"]95.5[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]Name[/TD]
[TD="class: xl64, width: 64"]Hours[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]Bob[/TD]
[TD="class: xl65, width: 64"]10.5[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]Bob[/TD]
[TD="class: xl65, width: 64"]15[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]Bob[/TD]
[TD="class: xl65, width: 64"]21.5[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]Bob[/TD]
[TD="class: xl65, width: 64"]12[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]Bob[/TD]
[TD="class: xl65, width: 64"]7[/TD]
[/TR]
</tbody>[/TABLE]

 

[Bob_ipc]

I get the same result as you have posted, 95.5 total hours. But when I calculate in manually should I not come out to 100.5 hours? Or am I missing something huge?