I'm currently working
on a project which consist of 10 worksheets (sandwiched between Start and End
sheets) and a Summary sheet at the beginning. All 10 sheets are formatted the
same, all consisting of the following table BUT updated on a weekly basis
(i.e. rows keep increasing until week 52).

A B C D
Y -0.5 0 2
W +0.5 0 2
W +0.5 1 0
Y -0.5 1 1
Y +0.5 1 2


Using the above table (note: A-D does not count as row), the formula cell
will check for the following conditions:
1) SEQUENCE OF TWO (2) ZEROS (with POSITIVE result)
a) TWO (2) consecutive 0s are met in column C, then if match;
b) checks the following row (row 3 for above) if cell B3 is positive digit
(+0.5 in this case), then if match;
c) it takes (C3-D3)+B3 ,i.e. in example above is (0-0)+(-0.5) = +0.5;
d) if value returned is positive (as above) then it counts as 1 occurence.
As the row keeps adding, and whenever conditions (a) to (c) are met, the
formula cell will keep counting to the total times it returned a positive
value.

Now here's the twist. IF C3 has the digit 0, the formula must still be able to detect the first two sequence of 0s (i.e. C1:C2 and still ignore C3 even if it is 0) and calculate according to the steps listed above.

Currently i have this formula but it doesn't seem to work at times
(returning #N/A or missing some counts on some of the worksheets):

=SUM(IF((FREQUENCY(IF(T7:T45=0,ROW(T7:T45)),IF(T7:T45<>0,ROW(T7:T45),0))=2)*(1-FREQUENCY(2,--T7:T45)),(S7:S45<0)*(T7:T45-V7:V45+S7:S45>0)))

Any gurus here to help?