Stopping a CountIf

[Don Kline]

In a column I need to count how many rows from the start of the column are
greater then zero. The trick is once the value is a 0 or a negative, it is to
stop counting.

Maximum number is 121.

I have a macro that does this but am looking for a way to do this through a
formula.

 

[T. Valko]

If I understand what you're wanting to do, try this...

Assuming the data is in a contiguous block (no empty/blank cells within the
range)

Array entered** :

=IF(COUNTIF(A2:A200,"<=0"),MATCH(TRUE,A2:A200<=0,0)-1,"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[T. Valko]

If I understand what you're wanting to do, try this...

Assuming the data is in a contiguous block (no empty/blank cells within the
range)

Array entered** :

=IF(COUNTIF(A2:A200,"<=0"),MATCH(TRUE,A2:A200<=0,0)-1,"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Sheeloo]

With your numbers in Col A, try this in B1
=MIN(IF(A1:A21<=0,ROW(A1:A20),""))
adjust 21 to your last row and press CTRL-SHIFT-ENTER after typing/pasting
the formula as it is an array formula

 

[Sheeloo]

With your numbers in Col A, try this in B1
=MIN(IF(A1:A21<=0,ROW(A1:A20),""))
adjust 21 to your last row and press CTRL-SHIFT-ENTER after typing/pasting
the formula as it is an array formula

 

[Don Kline]

Thanks for the solution. It solves my problem via a formula and I can ditch
the macro.

 

[Don Kline]

It returned a 3 when I was expecting 20. I'll play with it some more. Thanks
for the reply.

 

[T. Valko]

You're welcome. Thanks for the feedback!