# Sum Numbers up to Number in Top Column

#### RaMDT

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

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

 Customer A Customer B 20 120 Product 1 10 10 Product 2 10 10 Product 3 80 80 Product 4 10 10 Product 5 10 10 Product 6 10 10 Product 7 10 10

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

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### levy77

##### Board Regular
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
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
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>
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>

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

##### Board Regular
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
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
(Couldn't edit my last reply) And no, I shouldn't have any 0's in the B column.

#### RaMDT

##### New Member
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):

 A B C D E F G H I 1 Cust A Cust B Cust C Cust D Cust E Cust F Cust G 2 20 120 870 10 20 -10 10 3 4 Product 1 10 10 -10 5 Product 2 10 10 6 Product 3 80 80 7 Product 4 10 10 8 Product 5 10 10 9 Product 6 10 10 10 Product 7 10 10 11 Product 8 360 360 12 Product 9 200 200 13 Product 10 160 160 14 Product 11 100 100 15 Product 12 20 20 16 Product 13 10 10 17 Product 14 10 10 18 Product 15 10 10 19 Product 16 10 10 20 Product 17 10 10 21 Product 18 10 10 10 22 Product 19 -10 -10 -10 -10 -10 -10 -10 -10 23 Product 20 10 10 10 10 10 10 10 24 Product 21 10 10

<tbody>
</tbody>

#### levy77

##### Board Regular
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
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.

1,102,645
Messages
5,488,077
Members
407,623
Latest member
Deigs

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...