Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stopping a userform subroutine | Excel Discussion (Misc queries) | |||
stopping code | Excel Discussion (Misc queries) | |||
Stopping a formula | Excel Discussion (Misc queries) | |||
Stopping Calculation | Excel Discussion (Misc queries) | |||
Stopping A Sheet From Scrolling | New Users to Excel |