Count cells only if three in a row meet criteria

I have a problem that I need some help with. I am working on an alert system to trip when three bad weeks in a row have occured (new data will be coming in on a weekly basis). The following is that I want to be able to do:

I have a cell on its own sheet that I want to display a value of 3 only if the cells (arranged in a single column) on a difference sheet contain a value of >0, three times in a row. For example:

Cells on sheet B:
-1
2
-4
2
3
1
Thus cell on sheet A = 3

I also want the cell on sheet A to continue counting each additional consecutive cell with a value >0 and add a value of 1 for every occurance. However, if the next cell down in the column contains a value <0, I want the cell in sheet A to return to default (value of zero or anything) and stay that way until another three positive number cells in a row occur. For example:

Cells on sheet B:
-1
2
3
4
4
1
Thus cell on sheet A = 5

or

Cells on sheet B:
-1
4
3
5
-1
Thus cell on sheet A = 0

If anyone has any ideas, your help would be much appriciated. I am definately stuck on this one.

Thanks!

assuming your numbers are in sheet B A1:A50 and there are no gaps try

=COUNT('Sheet B'!A1:A50)-LOOKUP(2,1/('Sheet B'!A1:A50<0),ROW('Sheet B'!A1:A50))

Let your data on SheetB start in A2. Then in B2 and copy down,

=IF(A2<0, 0, B1 + 1)

Then the formula on Sheet A is

=INDEX(SheetB!$B$2:$B$65536, COUNTA(SheetB!$B$2:$B$65536))

That's a very nice solution, daddylonglegs. Can it be modified so it works if there are no negative values?

Originally Posted by shg

.....Can it be modified so it works if there are no negative values?

Now you're just being picky

One way........

=COUNT('Sheet B'!A1:A50)-LOOKUP(2^20+1,CHOOSE({1,2},0,LOOKUP(2,1/('Sheet B'!A1:A50<0),ROW('Sheet B'!A1:A50))))

Your use of Lookup with the literal array is very clever; it took me a while to parse it mentally. You even made it Excel 2007-compatible for a million rows.

Nice.