On Feb 23, 11:48*am, "joeu2004" wrote:
"fzl2007" wrote:
=COUNT($A$1:$A$10)
-MATCH(2,1/(SIGN(OFFSET($A$10,-1,0))<SIGN(OFFSET($A$1,1,0,COUNT($A$1:$A$10**)))) )
[....]
The column data will be added and the fomula needs to
capture the new data added. There are many such columns.
This will not be a good solution as we will need to
update the formula every time when data changes.

That depends on how you are capturing the data. *Again, if you can
__insert__ data between A1 and A10, the formula will adjust automagically..

More generally, assuming that column B contains only your data starting in
row 2 (specially, no numeric data after; text before and after is okay), you
could use the following __array_formula__ (press ctrl+shift+Enter instead of
just Enter):

=COUNT(B2:INDEX(B:B,MATCH(1E+300,B:B)))
- MATCH(2,1/(SIGN(INDEX(B:B,MATCH(1E+300,B:B)))
<SIGN(B2:INDEX(B:B,MATCH(1E+300,B:B)))))

Note: *Given the assumptions, you could write COUNT(B:B) instead. *I wanted
to show the more general form just in case some of the assumptions do not
apply.

That is an extremely inefficient formula, particularly in XL2007 and later
with a limit 1M rows (1M = 1,048,576).

If you know your data will not extend to more than row 10,000, it would be
much better to replace B:B with B1:B10000. *(Note: *B1, not B2.)

It would be even better if you put =MATCH(1E300,B1:B10000) into a helper
cell (X1), and replace the repeated MATCH expressions with X1 in the
formula.

And if all of the "many such columns" are the same length, it would be
better if you also put =COUNT(B2:INDEX(B:B,X1)) into a helper cell (X2)..

Thus, you formula becomes:

=X2-MATCH(2,1/(SIGN(INDEX(B:B,X1))<SIGN(B2:INDEX(B:B,X1))))

The good news is: *INDEX is not a volatile function. *The formula is
recalculated only when column B changes.

However, it appears that Excel does recalculate formulas with INDEX when the
file is opened. *So you might experience some delay at the outset; and you
will be prompted to save or not when you close the file, even if you did not
make any changes yourself.

Hi Joe,
This worked like a charm,

=COUNT(B2:INDEX(B:B,MATCH(1E+300,B:B)))
- MATCH(2,1/(SIGN(INDEX(B:B,MATCH(1E+300,B:B)))
<SIGN(B2:INDEX(B:B,MATCH(1E+300,B:B)))))

Now. I have data on "Sheet1" and want results on "Sheet2". How do I
apply your formula so that it will look up every other column starting
Column B on "Sheet1" and place the result on F6
"Sheet2", Column D on "Sheet1" and place result on F7 "Sheet2";
Column
F on "Sheet1" and place result on F8 on "Sheet2" and on ... until
column is empty?

Thanks again.
Faye