Sum Numbers up to Number in Top Column

RaMDT

New Member
Joined
Jun 3, 2019
Messages
6
Complicated but... here's the final product I need:

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

Customer ACustomer B
20120
Product 11010
Product 21010
Product 38080
Product 41010
Product 51010
Product 61010
Product 71010

<tbody>
</tbody>

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

New Member
Joined
May 7, 2019
Messages
45
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

Well-known Member
Joined
May 8, 2018
Messages
849
Office Version
365, 2010
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,159
Excel 2012
ABCDE
1Customer ACustomer BCustomer C
22011550
3
4Product 1101000
5Product 2101000
6Product 3800800
7Product 4100100
8Product 5100100
9Product 6100100
10Product 710055
11Product 8200020
12Product 90000

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet8

Worksheet Formulas
CellFormula
C4=MIN(B4,C$2-SUM(C$3:C3))
D4=MIN(MAX(SUM($B$4:$B4)-SUM($C$3:C$12),0),$B4,D$2-SUM(D$3:D3))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



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

New Member
Joined
May 7, 2019
Messages
45
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

New Member
Joined
Jun 3, 2019
Messages
6
Excel 2012
ABCDE
1Customer ACustomer BCustomer C
22011550
3
4Product 1101000
5Product 2101000
6Product 3800800
7Product 4100100
8Product 5100100
9Product 6100100
10Product 710055
11Product 8200020
12Product 90000

<tbody>
</tbody>
Sheet8

Worksheet Formulas
CellFormula
C4=MIN(B4,C$2-SUM(C$3:C3))
D4=MIN(MAX(SUM($B$4:$B4)-SUM($C$3:C$12),0),$B4,D$2-SUM(D$3:D3))

<tbody>
</tbody>

<tbody>
</tbody>



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

New Member
Joined
Jun 3, 2019
Messages
6
(Couldn't edit my last reply) And no, I shouldn't have any 0's in the B column.
 

RaMDT

New Member
Joined
Jun 3, 2019
Messages
6
Excel 2012
ABCDE
1Customer ACustomer BCustomer C
22011550
3
4Product 1101000
5Product 2101000
6Product 3800800
7Product 4100100
8Product 5100100
9Product 6100100
10Product 710055
11Product 8200020
12Product 90000

<tbody>
</tbody>
Sheet8

Worksheet Formulas
CellFormula
C4=MIN(B4,C$2-SUM(C$3:C3))
D4=MIN(MAX(SUM($B$4:$B4)-SUM($C$3:C$12),0),$B4,D$2-SUM(D$3:D3))

<tbody>
</tbody>

<tbody>
</tbody>



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):

ABCDEFGHI
1Cust ACust BCust CCust DCust ECust FCust G
2201208701020-1010
3
4Product 11010-10
5Product 21010
6Product 38080
7Product 41010
8Product 51010
9Product 61010
10Product 71010
11Product 8360360
12Product 9200200
13Product 10160160
14Product 11100100
15Product 122020
16Product 131010
17Product 141010
18Product 151010
19Product 161010
20Product 171010
21Product 18101010
22Product 19-10-10-10-10-10-10-10-10
23Product 2010101010101010
24Product 211010

<tbody>
</tbody>
 

levy77

New Member
Joined
May 7, 2019
Messages
45
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

New Member
Joined
Jun 3, 2019
Messages
6
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.
 

Forum statistics

Threads
1,085,419
Messages
5,383,552
Members
401,836
Latest member
Bweston07

Some videos you may like

This Week's Hot Topics

Top