Count consecutive positive and negative numbers in a column (with text) in a filtered sheet

[opa1]

Hi,

I want to count the consecutive positive and negative numbers in a column.
My issue is that I want that in a filtered sheet and also with text ("") below the numbers I want to count in.
Column A has the original data in let’s say sheet1. Then I copy the data into sheet2 like I do in column C.
With the helper column E I can get it to count the pos and neg incidents, but with #VALUE! because if the way I am doing it in column C.
After that the real result in column H cannot calculate the result and also returns #value

Any idea what should be different?
I think this is also not the right way to do it for a filtered sheet, right?
I want the result to be updated if the sheet is filtered and eg. only rows 2-11 are displayed.

Thank you very much in advance for your help!

consecutive_pos_neg.ods (113.2 KB)

[karolus]

to answer your question:

=IFERROR(IF(SIGN(C3)=SIGN(C2);E2+SIGN(C3);SIGN(C3));"")

but your Data is a complete broken mess, especially the wrong textual dates, and the false Calculation of years, month, …

[opa1]

Thank you!
And I am happy that you pointed that out!
My sheet is only a combination of solutions I found for my issues and they are mostly not optimal. Maybe they work for now, but will cause problems in the future. I am not a calc pro at all.
What would you suggest instead in particular?

Edit:
Any idea how to adapt your formula to work for a filtered column?
Maybe the helper column is not a good idea in this case.