Sum Numbers up to Number in Top Column

[RaMDT]

Complicated but... here's the final product I need:

Bold = data I have
non-bold = data how I need it to show

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Customer A[/TD]
[TD]Customer B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]20[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product 3[/TD]
[TD]80[/TD]
[TD][/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]Product 4[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Product 5[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Product 6[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Product 7[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

Customer A gets the data from column #2(B) up to 20 (10+10)
then because Customer A is done w/ 20, it goes to Customer B
Customer B gets the data from column #2(B) up to 120 (80+10+10+10+10)

A bit complicated to explain but I hope you get it. I'll happily answer any questions. I'm OK w/ VBA. Thank you!!!

 

[levy77]

Hi RaMDT,

In C3 try the formula =IF(SUM($B$3:B3)<=$C$2,B3,"").
And in D3 try the formula =IF(SUM($B$3:B3)>$C$2,B3,"").

Drag both these down and it should give you what you need.

Is this what you were after?

 

[kweaver]

levy77's formula in D will work as long as the OP doesn't have additional rows below product 7 that then might be cause to have another column (customer C).

 

[Eric W]

Excel 2012

	A	B	C	D	E
Customer A	Customer B	Customer C
Product 1
Product 2
Product 3
Product 4
Product 5
Product 6
Product 7
Product 8
Product 9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]80[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]=MIN(B4,C$2-SUM(C$3:C3))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4[/TH]
[TD="align: left"]=MIN(MAX(SUM($B$4:$B4)-SUM($C$3:C$12),0),$B4,D$2-SUM(D$3:D3))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



This should handle multiple customers. I added a blank row in 3 to make the formulas work. Also, any values in the B column MUST be populated, empty cells will cause the formulas to go awry (but I can change that if need be). Just put in the C4 formula and drag down. Then put in the D4 formula and drag down and across as needed (changing ranges as necessary).

 

[levy77]

Also, You could use =IF(B$2<>0,IF(AND(SUM(B$2:$C$2)<SUM($B$3:$B3),SUM($C$2:C$2)>=SUM($B$3:$B3)),$B3,""),IF(SUM($B$3:$B3)<=C$2,$B3,"")) for all the columns.

 

[RaMDT]

Excel 2012

	A	B	C	D	E
Customer A	Customer B	Customer C
Product 1
Product 2
Product 3
Product 4
Product 5
Product 6
Product 7
Product 8
Product 9

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]80[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C4[/TH]
[TD="align: left"]=MIN(B4,C$2-SUM(C$3:C3))[/TD]
[/TR]
[TR]
[TH]D4[/TH]
[TD="align: left"]=MIN(MAX(SUM($B$4:$B4)-SUM($C$3:C$12),0),$B4,D$2-SUM(D$3:D3))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



This should handle multiple customers. I added a blank row in 3 to make the formulas work. Also, any values in the B column MUST be populated, empty cells will cause the formulas to go awry (but I can change that if need be). Just put in the C4 formula and drag down. Then put in the D4 formula and drag down and across as needed (changing ranges as necessary).

Eric, thank you SO much, this almost worked perfectly!! You were right about the customers, I do have several. One thing I didn't mention in my OG post: I could have negative numbers in my B column. That's what's messing up the formula in 2 whole rows because in column B I have -10 and 10 (Cust X has -10 and Cust Y has 10, for example).

 

[RaMDT]

(Couldn't edit my last reply) And no, I shouldn't have any 0's in the B column.

 

[RaMDT]

Excel 2012

	A	B	C	D	E
Customer A	Customer B	Customer C
Product 1
Product 2
Product 3
Product 4
Product 5
Product 6
Product 7
Product 8
Product 9

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]80[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C4[/TH]
[TD="align: left"]=MIN(B4,C$2-SUM(C$3:C3))[/TD]
[/TR]
[TR]
[TH]D4[/TH]
[TD="align: left"]=MIN(MAX(SUM($B$4:$B4)-SUM($C$3:C$12),0),$B4,D$2-SUM(D$3:D3))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



This should handle multiple customers. I added a blank row in 3 to make the formulas work. Also, any values in the B column MUST be populated, empty cells will cause the formulas to go awry (but I can change that if need be). Just put in the C4 formula and drag down. Then put in the D4 formula and drag down and across as needed (changing ranges as necessary).

And maybe also an example of when it gets messy (Red = shouldn't be there):

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Cust A[/TD]
[TD="align: right"]Cust B[/TD]
[TD="align: right"]Cust C[/TD]
[TD="align: right"]Cust D[/TD]
[TD="align: right"]Cust E[/TD]
[TD="align: right"]Cust F[/TD]
[TD="align: right"]Cust G[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]870[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]Product 1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]Product 2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]Product 3[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"][/TD]
[TD="align: right"]80[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]Product 4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]Product 5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]Product 6[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]Product 7[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]Product 8[/TD]
[TD="align: right"]360[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]360[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]Product 9[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]200[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]Product 10[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]160[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]Product 11[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]Product 12[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]Product 13[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]Product 14[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]Product 15[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD="align: right"]Product 16[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]Product 17[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD="align: right"]Product 18[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD="align: right"]Product 19[/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]-10[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD="align: right"]Product 20[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]Product 21[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[levy77]

Try =IF(B$2<>0,IF(AND(SUM(B$2:$C$2)<sum($b$3:$b3),sum($c$2:c$2) style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">=SUM($B$3:$B3)),$B3,""),IF(SUM($B$3:$B3)<=C$2,$B3,"")), it should account for negative numbers.</sum($b$3:$b3),sum($c$2:c$2)>

 

[RaMDT]

Try =IF(B$2<>0,IF(AND(SUM(B$2:$C$2)<sum($b$3:$b3),sum($c$2:c$2) style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">=SUM($B$3:$B3)),$B3,""),IF(SUM($B$3:$B3)<=C$2,$B3,"")), it should account for negative numbers.</sum($b$3:$b3),sum($c$2:c$2)>

Hi Levy, I had tried it before I tried Eric's but it didn't work.