Limit sumif total to set maximum

[gogirlmp]

I am calculating employees PTO hours each pay period, a starting balance, earned hours and requested time off. Some employees will request up to 14 days off within the pay period but the total subtracted needs to be limited to 80 hours.

My formula works in calculating a running PTO balance except for the cap on used hours. How to I add the limit the requested hours to 80.

D13=previous PTO balance, M2=starting PTO balance, G2:G27=the pay periods, H2:h27=the hours requested in each pay period and A14=the current pay period.

=D13+$M$2-SUMIF($G$2:$G$27,A14,$H$2:$H$27)

(A) (D) (G) (H)
[TABLE="width: 1399"]
<tbody>[TR]
[TD]Pay Period[/TD]
[TD]PayPeriod Start Date[/TD]
[TD]PTO Running Total[/TD]
[TD]PTO Start Date[/TD]
[TD]PTO PP Requested[/TD]
[TD]PTO hours used[/TD]
[TD][/TD]
[TD]Pay Period Start[/TD]
[TD]Current PP PTO Balance[/TD]
[TD]Earning Rate/hrs[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2/18/2018[/TD]
[TD]44[/TD]
[TD]2/18/2018[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]2/18/2018[/TD]
[TD]59[/TD]
[TD]9.241[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3/4/2018[/TD]
[TD]37[/TD]
[TD]2/24/2018[/TD]
[TD]5[/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]3/18/2018[/TD]
[TD]23[/TD]
[TD]3/4/2018[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]4/1/2018[/TD]
[TD]24[/TD]
[TD]3/11/2018[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]4/15/2018[/TD]
[TD]-23[/TD]
[TD]3/18/2018[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]4/29/2018[/TD]
[TD]-14[/TD]
[TD]3/24/2018[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]5/13/2018[/TD]
[TD]-4[/TD]
[TD]3/25/2018[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]5/27/2018[/TD]
[TD]5[/TD]
[TD]4/9/2018[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]6/10/2018[/TD]
[TD]6[/TD]
[TD]4/19/2018[/TD]
[TD]9[/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]6/24/2018[/TD]
[TD]15[/TD]
[TD]4/21/2018[/TD]
[TD]9[/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]7/8/2018[/TD]
[TD]17[/TD]
[TD]4/23/2018[/TD]
[TD]9[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]7/22/2018[/TD]
[TD]-6[/TD]
[TD]4/28/2018[/TD]
[TD]9[/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]8/5/2018[/TD]
[TD]-85[/TD]
[TD]6/10/2018[/TD]
[TD]13[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]8/19/2018[/TD]
[TD]-100[/TD]
[TD]7/14/2018[/TD]
[TD]15[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]9/2/2018[/TD]
[TD]-90[/TD]
[TD]7/27/2018[/TD]
[TD]16[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]9/16/2018[/TD]
[TD]-81[/TD]
[TD]7/28/2018[/TD]
[TD]16[/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]9/30/2018[/TD]
[TD]-72[/TD]
[TD]7/30/2018[/TD]
[TD]16[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]10/14/2018[/TD]
[TD]-63[/TD]
[TD]8/9/2018[/TD]
[TD]17[/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]10/28/2018[/TD]
[TD]-61[/TD]
[TD]8/11/2018[/TD]
[TD]17[/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]11/11/2018[/TD]
[TD]-60[/TD]
[TD]8/13/2018[/TD]
[TD]17[/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]11/25/2018[/TD]
[TD]-51[/TD]
[TD]8/18/2018[/TD]
[TD]17[/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]12/9/2018[/TD]
[TD]-42[/TD]
[TD]8/20/2018[/TD]
[TD]18[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]12/23/2018[/TD]
[TD]-32[/TD]
[TD]8/21/2018[/TD]
[TD]18[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Thank you in advance for your assistance

 

[arthurbr]

Perhaps =D13+$M$2-MIN(SUMIF($G$2:$G$27,A14,$H$2:$H$27),80)

 

[jmacleary]

Hi there.
You could code it like this: =D13+$M$2-IF(SUMIF($G$2:$G$27,A14,$H$2:$H$27)>=80,80,SUMIF($G$2:$G$27,A14,$H$2:$H$27))
but I prefer arthur's answer!

 

[gogirlmp]

Thank you arthurbr that worked perfect.

 

[arthurbr]

You're welcome