Results 1 to 11 of 11
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Sum all up to negative number (Excel All)

    Hi,

    Imagine a column of numbers starting with negative values as financial output costs to make a product. Then these numbers become increasingly positive as the product sells. However, when the product becomes older and excitement dwindles the product does not sell anymore and eventually the sales become negative again. So the product is discontinued. I need to sum up the total value (profit) earned, starting at the initial negative costs right up to the last positive number. How do I do this?

    For sake of the post, assume I have the product values history listed in the A column from A3 to A20. How do I start calculating a sum from A3:A20, but ignoring the negative values that (for argument sake) start in A15 through to A20.

    Hope this is clear...
    TX
    Regards,
    Rudi

  2. #2
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Sum all up to negative number (Excel All)

    Try something like <pre>=SUMIF(A$:A$20,">=0",A$2:A20)</pre>

    If they are greater than zero, sum them else ignore.
    Alan

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum all up to negative number (Excel All)

    Hi MacroAlan,

    TX for the quick response, but its not just the positive numbers that must be added. I need the initial costs starting in A3 up to but excluding the first negative number. This negaitve can occur anywhere down the column that ends at A20, not at a fixed position.

    TX

    EG:

    -10
    -4
    1
    7
    8
    10
    12
    6
    3
    -1
    -3
    -9

    Sum from -10 all the way through to 3 (just above -1)
    Regards,
    Rudi

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum all up to negative number (Excel All)

    Rudi
    In this current example you have does the -10 reside in cell A3?

    If not an excel wb attachment example will make this easier
    Jerry

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sum all up to negative number (Excel All)

    <P ID="edit" class=small>(Edited by sdckapr on 07-Feb-08 14:28. Corrected error in formula)</P>How about this array? (confirm with ctrl-shift-enter):

    =SUM(OFFSET($A$3,0,0,MATCH(TRUE,($A$3:$A$20<0)*ROW ($A$3:$A$20)>(ROW($A$3)-1+MATCH(TRUE,$A$3:$A$20>0,0)),0)-1,1))

    Steve

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum all up to negative number (Excel All)

    Dunno how you do it, but yep...that works great!
    Many TX.

    Jezza, I guess that sample workbook is not needed, but I'll be glad to send it if you still want! <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    Regards,
    Rudi

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Sum all up to negative number (Excel All)

    If you don't have to consider the possibility of 0 followed by one or more positives followed by a zero followed by one or more positives, then you could use this non-array version:
    =SUM(A1:INDEX(A1:A12,MATCH(0,A1:A12,1)+COUNTIF(A1: A12,">0")))
    FWIW!
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sum all up to negative number (Excel All)

    I don't know if this is an issue, but once the 1st negative comes following the positive, if there are any more positive values afterwards, the 2 methods will lead to different numbers. Mine will ignore them, yours will include some of the initial few of these negative numbers....

    For example (and I don't know if this is possible in the datasets) if you have the values:
    -10
    -4
    1
    7
    8
    10
    12
    6
    3
    -1
    -3
    -9
    1
    2

    My scheme will sum from the -10 to the 3 right before the -1. Yours will sum from the -10 but also the -1 and -3...

    Steve

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Sum all up to negative number (Excel All)

    That is true but i discounted it as the implication seemed to be that once the sales went negative again, the product was discontinued. If there might be positive numbers again, then it is an issue as you say.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sum all up to negative number (Excel All)

    Yes it does seem implicit in the description, but I have been "burned" by these type of inferences in the past. I was worried about it essentially getting to zero but varying between negative and positive just as random variation. I have seen trending data look like that with the description.

    Steve

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum all up to negative number (Excel All)

    Rory,

    TX for your example too. The reason for the post is that it was asked by a customer of ours, and based on the setup of their spreadsheets, either of these solutions will come into play. They just gave me a basic description of their problem initially, which I explained in the starting thread.

    TX both!
    Regards,
    Rudi

Posting Permissions

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