Change cells' amounts for single pay day to be less than Max Limit for pay Day

[KevinA626]

Hello all,

I know the title isn't the best, but I can't think of how to word it. Basically, the software I'm using is not supposed to pay the employees more than x amount (in the example I will provide, x amount = 1,000). The program exports an excel file with the name in column a, the week ending date in column b, and the amount in column c.

Name	Week Ending	Amount
Smith, John	9/23/2017	268
Smith, John	9/23/2017	252
Smith, John	9/23/2017	0
Smith, John	9/23/2017	0
Smith, John	9/23/2017	0
Smith, John	9/30/2017	250
Smith, John	9/30/2017	250
Smith, John	9/30/2017	250
Smith, John	9/30/2017	250
Smith, John	9/30/2017	250
Smith, John	10/7/2017	50
Smith, John	10/7/2017	45
Smith, John	10/7/2017	50
Smith, John	10/7/2017	40
Smith, John	10/14/2017	500
Smith, John	10/14/2017	500

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Basically, the amounts for each week ending date cannot surpass x amount = 1000 (they can equal 1000, just not surpass). Instead, the sum of the amounts for each work ending date have to be edited to equal 1000. Below is an example of how I changed on employee compared to how he was listed in the above example. If you notice, 9/30/2017's amounts were more than 1000 in the above example, but I changed them to equal 1000 in the below example. There is no max for an individual amount. There is only a max for week ending date total amounts. Also, if the week ending date total amount is less than 1000, I would like the original amounts to remain.

Name	Week Ending	Amount
Smith, John	9/23/2017	288
Smith, John	9/23/2017	252
Smith, John	9/23/2017	0
Smith, John	9/23/2017	0
Smith, John	9/23/2017	0
Smith, John	9/30/2017	450
Smith, John	9/30/2017	300
Smith, John	9/30/2017	250
Smith, John	9/30/2017
Smith, John	9/30/2017
Smith, John	10/7/2017	50
Smith, John	10/7/2017	45
Smith, John	10/7/2017	50
Smith, John	10/7/2017	40
Smith, John	10/14/2017	500
Smith, John	10/14/2017	500

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Momentman]

If you just want to blank the amount entries once 1000 is reached, then maybe

Excel 2010
	A	B	C	D
1	Name	Week Ending	Amount	Revised
2	Smith, John	9/23/2017	268	268
3	Smith, John	9/23/2017	252	252
4	Smith, John	9/23/2017	0	0
5	Smith, John	9/23/2017	0	0
6	Smith, John	9/23/2017	0	0
7	Smith, John	9/30/2017	250	250
8	Smith, John	9/30/2017	250	250
9	Smith, John	9/30/2017	250	250
10	Smith, John	9/30/2017	250	250
11	Smith, John	9/30/2017	250
12	Smith, John	10/07/2017	50	50
13	Smith, John	10/07/2017	45	45
14	Smith, John	10/07/2017	50	50
15	Smith, John	10/07/2017	40	40
16	Smith, John	10/14/2017	500	500
17	Smith, John	10/14/2017	500	500
Sheet2

Cell Formulas
Range	Formula
D2	=IF(SUMIF($B$2:B2,B2,$C$2:C2)>1000,"",C2)

 

[KevinA626]

If you just want to blank the amount entries once 1000 is reached, then maybe

Excel 2010#FFFFFF ;border: 1px solid;border-collapse: collapse; border-color: #BBB">#DAE7F5 " />#DAE7F5 ;text-align: center;color: #161120">

	A	B	C	D
1	Name	Week Ending	Amount	Revised
2	Smith, John	9/23/2017	268	268
3	Smith, John	9/23/2017	252	252
4	Smith, John	9/23/2017	0	0
5	Smith, John	9/23/2017	0	0
6	Smith, John	9/23/2017	0	0
7	Smith, John	9/30/2017	250	250
8	Smith, John	9/30/2017	250	250
9	Smith, John	9/30/2017	250	250
10	Smith, John	9/30/2017	250	250
11	Smith, John	9/30/2017	250
12	Smith, John	10/07/2017	50	50
13	Smith, John	10/07/2017	45	45
14	Smith, John	10/07/2017	50	50
15	Smith, John	10/07/2017	40	40
16	Smith, John	10/14/2017	500	500
17	Smith, John	10/14/2017	500	500

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

#DAE7F5 ;color: #161120">Sheet2​

#FFFFFF " >

Worksheet Formulas#FFFFFF ;border-collapse: collapse; border-color: #BBB">#DAE7F5 ;color: #161120"> Cell Formula #DAE7F5 ;color: #161120">D2 =IF(SUMIF($B$2:B2,B2,$C$2:C2)>1000,"",C2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Momentman, Thank you for your reply! Although, I forgot to include the criteria of names changing (in column A). If the name on the left changes, the formula would be affected, correct? Basically, the name & date combination cannot have more than 1,000 total value.

Do you have any suggestions! Thanks again!

 

[KevinA626]

Hi Momentman,

I figured out how to use multiple criteria (name & date and not just date) when calculating the sum of values - I used the SUMIFS formula.

I do have another question you may be able to help with.

If, for example, the value is zero'd out by the following value (as show in bold in Cells C2:C3 and D2:D3), I would like the formula to keep the numbers. Right now, what it is doing is taking out the positive number but leaving the negative number, making for a sum of -1100, in the example shown below, instead of 1100 + (-1100) = 0.

Do you have any suggestions?

Thank you!

If you just want to blank the amount entries once 1000 is reached, then maybe

Excel 2010#FFFFFF ;border: 1px solid;border-collapse: collapse; border-color: #BBB">#DAE7F5 " />#DAE7F5 ;text-align: center;color: #161120">

	A	B	C	D
1	Name	Week Ending	Amount	Revised
2	Smith, John	9/23/2017	1100	1100
3	Smith, John	9/23/2017	-1100	-1100
4	Smith, John	9/23/2017	0	0
5	Smith, John	9/23/2017	0	0
6	Smith, John	9/23/2017	0	0
7	Smith, John	9/30/2017	250	250
8	Smith, John	9/30/2017	250	250
9	Smith, John	9/30/2017	250	250
10	Smith, John	9/30/2017	250	250
11	Smith, John	9/30/2017	250
12	Smith, John	10/07/2017	50	50
13	Smith, John	10/07/2017	45	45
14	Smith, John	10/07/2017	50	50
15	Smith, John	10/07/2017	40	40
16	Smith, John	10/14/2017	500	500
17	Smith, John	10/14/2017	500	500

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

#DAE7F5 ;color: #161120">Sheet2​

#FFFFFF " >

Worksheet Formulas#FFFFFF ;border-collapse: collapse; border-color: #BBB">#DAE7F5 ;color: #161120"> Cell Formula #DAE7F5 ;color: #161120">D2 =IF(SUMIF($B$2:B2,B2,$C$2:C2)>1000,"",C2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>