Results 1 to 11 of 11

20080207, 20:47 #1
 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...
TXRegards,
Rudi

20080207, 20:57 #2
 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

20080207, 21:08 #3
 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

20080207, 21:18 #4
 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 easierJerry

20080207, 21:28 #5
 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 07Feb08 14:28. Corrected error in formula)</P>How about this array? (confirm with ctrlshiftenter):
=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

20080207, 22:29 #6
 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

20080208, 14:03 #7
 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 nonarray version:
=SUM(A1:INDEX(A1:A12,MATCH(0,A1:A12,1)+COUNTIF(A1: A12,">0")))
FWIW!Regards,
Rory
Microsoft MVP  Excel

20080208, 14:37 #8
 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

20080208, 14:50 #9
 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

20080208, 15:15 #10
 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

20080208, 19:38 #11
 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