Summing positive numbers before hitting a negative

Hello,

Let us say that this is a certain cash flow

Year - Cash flow
2007 - (200)
2008 - (100)
2009 - 50
2010 - 100
2011 - 200
2012 - (100)
2013 - (25)
2014 - 50
2015 - 300

For year 2007,2008,2012 and 2013, we have negative cash.

I want to compute for the investment cost, which I define to be the total accumulated cost before first positive cash. First positive cash occurs at 2009, so I want to sum 2007 and 2008, However, 2012 and 2013 also reports negative cash, so in the SUM formula, their values will be included.

I tried sumif, if arguments to no avail.

How should I write the formula in order to sum all the negative cash before positive cash, disregarding all the other cash flows thereafter (be it positive or negative)?

Many thanks,

lex

Hello Steven,

Thanks for the reply and I have to apologize for my confusing statement.

I want to sum all the negative values before the FIRST occurence of positive cash, which occurs at 2009. I don't want to include all the other succeeding values, be it positive or negative, so that the total will only show - 300. Thanks!

Lex

Here is one very quick way of doing it, assuming you can deal with hiding a column. Assuming we start at row 1, with year in column A, $$ in B, and using C as the column you will hide... We will make formulas that will produce 1's if its a value that should be summed, and 0's if it shouldn't.

In C1, use the formula "=IF(B1<0,1,0)" This will return 1 if it is negative, and if its positive, it will end up preventing anything else from summing.

In C2 and beyond, use the formula "=IF(AND(B3<0,C2=1),1,0)" In the first cell, this will check to see if the number is negative AND check to see if the number above was negative. Since this will be copied down as far as you need, it will produce a 0 with the first positive number it hits. Since it requires the preceding row to produce a 1 to possibly produce another one, it will prevent any row from ever producing a 1 again after the first positive is hit.

After this, just go to the cell in which you wanted the total to show up and do a normal SUMIF using the 1 as the criteria for summing. If you need help with the sumif, let me know, but the formula wizard is pretty good with this function.