"fzl2007" wrote:
I want to count the rows if the value of the column,
from bottom up, changes sign
[....]
For example,
-2.1 31.2 -2.1
0.3 44.8 1.6
-1.1 -9.4 5.3
44 10.4 9
13.1 -2.2 0
12.2 -11.1 3.2
counter=3 counter=2 counter=1

For the data in A1:A6, enter the following __array_formula__ (press
ctrl+shift+Enter instead of Enter):

=6-MATCH(2,1/(SIGN(A6)<SIGN(A1:A5)))

6 can be replaced by COUNT(A1:A6).

MATCH returns the last row number relative to A1 whose sign differs from A6.
6-MATCH(...) makes that row number relative to A6.

Caveat: This returns a #N/A if there is no sign change. If you do not like
that, you can do the following in XL2007 and later:

=IFERROR(6-MATCH(2,1/(SIGN(A6)<SIGN(A1:A5))),"")

In XL2003 and earlier:

=IF(SUMPRODUCT(--(SIGN(A6)<SIGN(A1:A5)))0,
6-MATCH(2,1/(SIGN(A6)<SIGN(A1:A5))),"")

Both alternatives are also __array_formulas__ (press ctrl+shift+Enter
instead of Enter).