Sum Values of an array to a set number

[Bduncan871]

i have weights of concrete mats in column "I"(I6:I100).

I need those to sum in column "J" only if they add up to a range between 46,000 & 48,000.

If rows 6 through 10 meet range, populate that sum in J column on rows 6-10, and so on all the way through row 100.

Is this possible?

 

[Scott T]

Excel 2010[TABLE="class: grid, width: 500"]
<colgroup><col><col><col></colgroup><tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]555.00[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]6,666.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]1,999.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]999.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]666.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]16,694.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]1,614.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"]17,000.00[/TD]
[TD="align: right"]46,193.00[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: right"]2,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: right"]4,444.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: right"]6,666.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: right"]9,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: right"]7,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: right"]10,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: right"]5,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: right"]3,000.00[/TD]
[TD="align: right"]47,110.00[/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="align: right"]9,999.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD="align: right"]35,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD="align: right"]1,001.00[/TD]
[TD="align: right"]46,000.00[/TD]
[/TR]
</tbody>[/TABLE]

Sheet1​

In J7 and copy down

=IF(AND(SUM($I$6:I7)-SUM($J$6:J6)>=46000,SUM($I$6:I7)-SUM($J$6:J6)<=48000),SUM($I$6:I7)-SUM($J$6:J6),IF(SUM($I$6:I7)-SUM($J$6:J6)>48000,"slabs add to over 48,000",""))

 

[Bduncan871]

Not working for me...


https://www.dropbox.com/s/fle02peks00ra92/load sheet.xlsx?dl=0

 

[Scott T]

I can not download files from dropbox at work.
How is it not working for you?

 

[Bduncan871]

Just gave me the "slabs add to over 48,000" value

 

[Scott T]

I did not get a change to look at the file last night. Could you post an example of your data and the formula you are using.

 

[Bduncan871]

https://ibb.co/kuriOw

 

[Bduncan871]

The values in "J" vary.

 

[Scott T]

Excel 2010[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TH][/TH]
[TH]H
[/TH]
[TH]I
[/TH]
[TH]J
[/TH]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: right"][/TD]
[TD]mat
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8,800.00
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8,800.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8,800.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8,800.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: right"]44,000.00
[/TD]
[TD="align: right"]8,800.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD="align: right"]52,800.00
[/TD]
[TD="align: right"]8,800.00
[/TD]
[TD]slabs add to over 48,000
[/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8,800.00
[/TD]
[TD]slabs add to over 48,000
[/TD]
[/TR]
[TR]
[TD="align: center"]13
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5,863.00
[/TD]
[TD]slabs add to over 48,000
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet4​

The total to row 10 is 44,000 which is under the 46,000 min but if you add the next Mat you get 52,800 which is over your max of 48,000. What should happen in this case?

Assuming it should sum to below the 46,000 min try.

=IF(AND(SUM($I$6:I7)-SUM($J$6:J6)>=46000,SUM($I$6:I7)-SUM($J$6:J6)<=48000),SUM($I$6:I7)-SUM($J$6:J6),IF(SUM($I$6:I8)-SUM($J$6:J6)>48000,SUM($I$6:I7)-SUM($J$6:J6),""))

 

[Bduncan871]

correct.. what it is is a truck load sheet.

legal cargo weight on a flat bed truck is 48,000lbs.

So it seems the new formula works as it should now.

any totals up to 48,000, populates in that cell like it is. if it goes over 48,000, it would just ignore it and put it on the next sequence.