Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Sum Numbers up to Number in Top Column

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Sum Numbers up to Number in Top Column

    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

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

  2. #2
    New Member
    Join Date
    May 2019
    Posts
    44
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum Numbers up to Number in Top Column

    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?

  3. #3
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum Numbers up to Number in Top Column

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

  4. #4
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,644
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Sum Numbers up to Number in Top Column

    Excel 2012
    A B C D E
    1 Customer A Customer B Customer C
    2 20 115 50
    3
    4 Product 1 10 10 0 0
    5 Product 2 10 10 0 0
    6 Product 3 80 0 80 0
    7 Product 4 10 0 10 0
    8 Product 5 10 0 10 0
    9 Product 6 10 0 10 0
    10 Product 7 10 0 5 5
    11 Product 8 20 0 0 20
    12 Product 9 0 0 0 0
    Sheet8

    Worksheet Formulas
    Cell Formula
    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))



    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).
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  5. #5
    New Member
    Join Date
    May 2019
    Posts
    44
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum Numbers up to Number in Top Column

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

  6. #6
    New Member
    Join Date
    Jun 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum Numbers up to Number in Top Column

    Quote Originally Posted by Eric W View Post
    Excel 2012
    A B C D E
    1 Customer A Customer B Customer C
    2 20 115 50
    3
    4 Product 1 10 10 0 0
    5 Product 2 10 10 0 0
    6 Product 3 80 0 80 0
    7 Product 4 10 0 10 0
    8 Product 5 10 0 10 0
    9 Product 6 10 0 10 0
    10 Product 7 10 0 5 5
    11 Product 8 20 0 0 20
    12 Product 9 0 0 0 0
    Sheet8

    Worksheet Formulas
    Cell Formula
    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))



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

  7. #7
    New Member
    Join Date
    Jun 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum Numbers up to Number in Top Column

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

  8. #8
    New Member
    Join Date
    Jun 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum Numbers up to Number in Top Column

    Quote Originally Posted by Eric W View Post
    Excel 2012
    A B C D E
    1 Customer A Customer B Customer C
    2 20 115 50
    3
    4 Product 1 10 10 0 0
    5 Product 2 10 10 0 0
    6 Product 3 80 0 80 0
    7 Product 4 10 0 10 0
    8 Product 5 10 0 10 0
    9 Product 6 10 0 10 0
    10 Product 7 10 0 5 5
    11 Product 8 20 0 0 20
    12 Product 9 0 0 0 0
    Sheet8

    Worksheet Formulas
    Cell Formula
    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))



    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

  9. #9
    New Member
    Join Date
    May 2019
    Posts
    44
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum Numbers up to Number in Top Column

    Try =IF(B$2<>0,IF(AND(SUM(B$2:$C$2)=SUM($B$3:$B3)),$B3,""),IF(SUM($B$3:$B3)<=C$2,$B3,"")), it should account for negative numbers.

  10. #10
    New Member
    Join Date
    Jun 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum Numbers up to Number in Top Column

    Quote Originally Posted by levy77 View Post
    Try =IF(B$2<>0,IF(AND(SUM(B$2:$C$2)=SUM($B$3:$B3)),$B3,""),IF(SUM($B$3:$B3)<=C$2,$B3,"")), it should account for negative numbers.
    Hi Levy, I had tried it before I tried Eric's but it didn't work.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •