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!!!

 

[kvsrinivasamurthy]

Try this. In C4 then dragged across.

=IF(AND($B4>0,C$2>0),IF(AND(SUMIF($B$4:$B4,">0")>SUMIF($B$2:B$2,">0"),SUMIF($B$4:$B4,">0")<=SUMIF($B$2:C$2,">0")),$B4,""),IF(AND($B4<0,C$2<0),IF(AND(SUMIF($B$4:$B4,"<0")<SUMIF($B$2:B$2,"<0"),SUMIF($B$4:$B4,"<0")>=SUMIF($B$2:C$2,"<0")),$B4,""),""))

What about product 20 and 21. They will not have any value in Cust columns.

 

[Eric W]

I'm struggling to understand how to interpret and handle negative numbers. Perhaps I should only use numbers in the calculations that have the same sign? So customers A-E and G ignore the -10 in B22, and customer F ignores everything except B22? And in your example from post 8, shouldn't I23 be a valid result (black not red)?

 

[RaMDT]

Try this. In C4 then dragged across.

=IF(AND($B4>0,C$2>0),IF(AND(SUMIF($B$4:$B4,">0")>SUMIF($B$2:B$2,">0"),SUMIF($B$4:$B4,">0")<=SUMIF($B$2:C$2,">0")),$B4,""),IF(AND($B4<0,C$2<0),IF(AND(SUMIF($B$4:$B4,"<0")<sumif($b$2:b$2,"<0"),sumif($b$4:$b4,"<0")>=SUMIF($B$2:C$2,"<0")),$B4,""),""))

What about product 20 and 21. They will not have any value in Cust columns.

This worked like a charm.

Thank you SO much, kvsrinivasamurthy & Eric W!!! </sumif($b$2:b$2,"<0"),sumif($b$4:$b4,"<0")>

 

[kvsrinivasamurthy]

Thanks for feedback