Stopping a CountIf
 

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.

Stopping a CountIf
 

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.

--
Biff
Microsoft Excel MVP


"Don Kline" wrote in message
...
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.

Stopping a CountIf
 

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" wrote:

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.

Stopping a CountIf
 

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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Don Kline" wrote in message
...
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.

Stopping a CountIf
 

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

"Sheeloo" wrote:

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" wrote:

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.

Stopping a CountIf
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Don Kline" wrote in message
...
Thanks for the solution. It solves my problem via a formula and I can
ditch
the macro.

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Don Kline" wrote in message
...
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.