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

K

KevinA626

New Member
Joined
Oct 9, 2017
Messages
3
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.

[TABLE="width: 231"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Week Ending[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]9/23/2017[/TD]
[TD="align: right"]268[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]9/23/2017[/TD]
[TD="align: right"]252[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]9/23/2017[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]9/23/2017[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]9/23/2017[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]9/30/2017[/TD]
[TD="align: right"]250[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]9/30/2017[/TD]
[TD="align: right"]250[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]9/30/2017[/TD]
[TD="align: right"]250[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]9/30/2017[/TD]
[TD="align: right"]250[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]9/30/2017[/TD]
[TD="align: right"]250[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]10/7/2017[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]10/7/2017[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]10/7/2017[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]10/7/2017[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]10/14/2017[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]10/14/2017[/TD]
[TD="align: right"]500[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 224"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Week Ending[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]9/23/2017[/TD]
[TD="align: right"]288[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]9/23/2017[/TD]
[TD="align: right"]252[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]9/23/2017[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]9/23/2017[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]9/23/2017[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]9/30/2017[/TD]
[TD="align: right"]450[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]9/30/2017[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]9/30/2017[/TD]
[TD="align: right"]250[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]9/30/2017[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]9/30/2017[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]10/7/2017[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]10/7/2017[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]10/7/2017[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]10/7/2017[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]10/14/2017[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD="align: right"]10/14/2017[/TD]
[TD="align: right"]500[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
If you just want to blank the amount entries once 1000 is reached, then maybe


Excel 2010
ABCD
1NameWeek EndingAmountRevised
2Smith, John9/23/2017268268
3Smith, John9/23/2017252252
4Smith, John9/23/201700
5Smith, John9/23/201700
6Smith, John9/23/201700
7Smith, John9/30/2017250250
8Smith, John9/30/2017250250
9Smith, John9/30/2017250250
10Smith, John9/30/2017250250
11Smith, John9/30/2017250
12Smith, John10/07/20175050
13Smith, John10/07/20174545
14Smith, John10/07/20175050
15Smith, John10/07/20174040
16Smith, John10/14/2017500500
17Smith, John10/14/2017500500
Sheet2
Cell Formulas
RangeFormula
D2=IF(SUMIF($B$2:B2,B2,$C$2:C2)>1000,"",C2)
 
Upvote 0
Momentman said:
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">
ABCD

<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>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Week Ending[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Amount[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Revised[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9/23/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]268[/TD]
[TD="align: right"]268[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9/23/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]252[/TD]
[TD="align: right"]252[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9/23/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9/23/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9/23/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9/30/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]250[/TD]
[TD="align: right"]250[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9/30/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]250[/TD]
[TD="align: right"]250[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9/30/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]250[/TD]
[TD="align: right"]250[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9/30/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]250[/TD]
[TD="align: right"]250[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9/30/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]250[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10/07/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]50[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10/07/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]45[/TD]
[TD="align: right"]45[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10/07/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]50[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10/07/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]40[/TD]
[TD="align: right"]40[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10/14/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]500[/TD]
[TD="align: right"]500[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10/14/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]500[/TD]
[TD="align: right"]500[/TD]

</tbody>
#DAE7F5 ;color: #161120">Sheet2​

#FFFFFF " >[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas#FFFFFF ;border-collapse: collapse; border-color: #BBB">#DAE7F5 ;color: #161120">[TABLE="width: 100%"]
<thead>[TR="bgcolor: <a href="]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: <a href="]#DAE7F5 ;color: #161120">D2[/TH]
[TD="align: left"]=IF(SUMIF($B$2:B2,B2,$C$2:C2)>1000,"",C2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
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!
Click to expand...
 
Upvote 0
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!

Momentman said:
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">
ABCD

<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>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Week Ending[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Amount[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Revised[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9/23/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"] 1100 [/TD]
[TD="align: right"] 1100 [/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9/23/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"] -1100 [/TD]
[TD="align: right"] -1100 [/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9/23/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9/23/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9/23/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9/30/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]250[/TD]
[TD="align: right"]250[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9/30/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]250[/TD]
[TD="align: right"]250[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9/30/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]250[/TD]
[TD="align: right"]250[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9/30/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]250[/TD]
[TD="align: right"]250[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9/30/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]250[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10/07/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]50[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10/07/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]45[/TD]
[TD="align: right"]45[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10/07/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]50[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10/07/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]40[/TD]
[TD="align: right"]40[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10/14/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]500[/TD]
[TD="align: right"]500[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith, John[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10/14/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]500[/TD]
[TD="align: right"]500[/TD]

</tbody>
#DAE7F5 ;color: #161120">Sheet2​

#FFFFFF " >[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas#FFFFFF ;border-collapse: collapse; border-color: #BBB">#DAE7F5 ;color: #161120">[TABLE="width: 100%"]
<thead>[TR="bgcolor: <a href="]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: <a href="]#DAE7F5 ;color: #161120">D2[/TH]
[TD="align: left"]=IF(SUMIF($B$2:B2,B2,$C$2:C2)>1000,"",C2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Rolling Count
Replies
1
Views
468
awoohaw
awoohaw
I
  • Question Question
Accrual Worksheet Next Pay date Formula
Replies
0
Views
266
ItalianPlatinum
I
A
  • Solved
lookupifs?? lookup value so long as date value is greater than specified date
Replies
5
Views
103
Peter_SSs
Peter_SSs
S
  • Question Question
Scan function in calculated field in excel pivot table
Replies
3
Views
276
RoryA
RoryA
WESTERNWALL
  • Question Question
Sum and Count functions
Replies
4
Views
238
WESTERNWALL
WESTERNWALL

Forum statistics

Threads
1,223,897
Messages
6,175,270
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top