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)

Pay Period	PayPeriod Start Date	PTO Running Total	PTO Start Date	PTO PP Requested	PTO hours used		Pay Period Start	Current PP PTO Balance	Earning Rate/hrs
5	2/18/2018	44	2/18/2018	5	8		2/18/2018	59	9.241
6	3/4/2018	37	2/24/2018	5	16
7	3/18/2018	23	3/4/2018	6	8
8	4/1/2018	24	3/11/2018	6	8
9	4/15/2018	-23	3/18/2018	7	8
10	4/29/2018	-14	3/24/2018	7	8
11	5/13/2018	-4	3/25/2018	7	8
12	5/27/2018	5	4/9/2018	8	8
13	6/10/2018	6	4/19/2018	9	16
14	6/24/2018	15	4/21/2018	9	16
15	7/8/2018	17	4/23/2018	9	8
16	7/22/2018	-6	4/28/2018	9	16
17	8/5/2018	-85	6/10/2018	13	8
18	8/19/2018	-100	7/14/2018	15	8
19	9/2/2018	-90	7/27/2018	16	8
20	9/16/2018	-81	7/28/2018	16	16
21	9/30/2018	-72	7/30/2018	16	8
22	10/14/2018	-63	8/9/2018	17	16
23	10/28/2018	-61	8/11/2018	17	16
24	11/11/2018	-60	8/13/2018	17	40
25	11/25/2018	-51	8/18/2018	17	16
26	12/9/2018	-42	8/20/2018	18	8
1	12/23/2018	-32	8/21/2018	18	8

<tbody>
</tbody>

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