Stopping a CountIf

  • Thread starter Thread starter Don Kline
  • Start date Start date
D

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.
 
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.
 
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.
 
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
 
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
 
Thanks for the solution. It solves my problem via a formula and I can ditch
the macro.
 
It returned a 3 when I was expecting 20. I'll play with it some more. Thanks
for the reply.
 
Back
Top