Created A Dynamic range with this formula
=offset(sheet1!$a$1,0,0,countif(sheet1!$a:$a,"<x" ),1) call it
MyRange

Col A:
1 AAA
2 BBB
3 CCC
4 x
5 x

IF I go to VBA and use Immediate Windows and I type:
? Range("MyRange").Rows.Count ................... I get
65531 ( I am using Excel 2007 )
My logic tells me it should be 3

Now if I go to A6 and type =CountIF(a1:a5,"<x")
I get 3

Can someone enlighten me ????

Hi Arishy,

I am assuming that with the result you are getting that you have the
workbook saved in 97-2003 format. Also I would expect the result to be 65534
not 65531.

Reason is that your countif counts all cells < "x" in column A and it will
include all of the blank cells because none of the blank cells are equal to
"x".


--
Regards,

OssieMac


"Arishy" wrote:

Created A Dynamic range with this formula
=offset(sheet1!$a$1,0,0,countif(sheet1!$a:$a,"<x" ),1) call it
MyRange

Col A:
1 AAA
2 BBB
3 CCC
4 x
5 x

IF I go to VBA and use Immediate Windows and I type:
? Range("MyRange").Rows.Count ................... I get
65531 ( I am using Excel 2007 )
My logic tells me it should be 3

Now if I go to A6 and type =CountIF(a1:a5,"<x")
I get 3

Can someone enlighten me ????
.

Presumably Cells a6:a65536 are empty and therefore are not = x and so get
counted.

Try something like
=offset(sheet1!$a$1,0,0,countif(sheet1!$a:$a,"<x" )-countif(sheet1!$a:$a,"="),1)

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Arishy" wrote in message
...
Created A Dynamic range with this formula
=offset(sheet1!$a$1,0,0,countif(sheet1!$a:$a,"<x" ),1) call it
MyRange

Col A:
1 AAA
2 BBB
3 CCC
4 x
5 x

IF I go to VBA and use Immediate Windows and I type:
? Range("MyRange").Rows.Count ................... I get
65531 ( I am using Excel 2007 )
My logic tells me it should be 3

Now if I go to A6 and type =CountIF(a1:a5,"<x")
I get 3

Can someone enlighten me ????

Afterthought. Try using
=OFFSET(Sheet1!$A$1,0,0,COUNTA($A:$A)-COUNTIF($A:$A,"=x"))

That should return the result you want.

--
Regards,

OssieMac

On Dec 31 2009, 11:35*am, OssieMac
wrote:
Afterthought. Try using
=OFFSET(Sheet1!$A$1,0,0,COUNTA($A:$A)-COUNTIF($A:$A,"=x"))

That should return the result you want.

--
Regards,

OssieMac

You are right, the WB was created for Excel 2003 hence the lower
number.
Blanks are not equal -x I missed that ...Thank you for pointing me to
the right direction.
As for your last Iteration....Worked like a charm....Have a lovely New
year.