CountA - stop counting in column when number changes positive or negative

Hello..... I can't figure this out. I have some columns of numbers which vary between positive and negative. Starting at the top of each column, I want to count down the number of cells (that are either positive or negative) and when the value in a cell changes sign, I want the count to stop and return how many cells the count got up to before being stopped. Hopefully this expalins... http://my.jetscreenshot.com/16785/20160425-zwrv-92kb
Many thanks

I probably should have posted this in the Formulas and Functions forum.......

Where would you the result to be display
How many columns to treat
It will be easier to attach an excel file to prepare the macro

Try this UDF
Somewhere put =Treat(B1:B18), where B1:B18 is the range to treat

Please Login or Register  to view this content.

A remake to care when the range has only one cell

Please Login or Register  to view this content.

Hi -

If you want to do this as a formula, try this:

{=IF(A1>0,SMALL(IF(SIGN($A$1:$A$23)<0,ROW($A$1:$A$23)),1),SMALL(IF(SIGN($A$1:$A$23)>0,ROW(A1:$A$23)),1))-ROW($A$1)}

This is an Array formula, so copy everything inside the curly braces {} but not the curly braces, and paste the formula into your worksheet where you want the answer to appear. Click in the formula edit window and press Ctrl-Shift-Enter at the same time so Excel makes this an array formula. Excel will add the curly braces.

Please note this formula is looking at a data set in the range of A1:A23. So you will have to edit the ranges to fit your data. The ROW function returns the row of the first time the data set changes sign. So you have to subtract the row number of the first value (the ROW function at the very end of the formula) to get the correct number.

Hope this helps.

No problem! Thanks for the bump! If you are satisfied with this answer, please edit the thread title to add the word SOLVED.

You are welcome,
Now I'm not sure that a formula is the best idea to save you time (effort).
I believe a macro collecting the data from all files and all sheets (as you need to do it with formulas) will be more useful
PCI