# Thread: Sum all up to negative number (Excel All)

1. ## 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

2. ## 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.

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

4. ## 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

5. ## 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. ## 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>

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

8. ## 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. ## 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.

10. ## 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. ## 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!

#### Posting Permissions

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