I'm using a simple Countif in XL2003 so don't need to CSE it. It fights with
a named range and I cannot see why. It worked when I set it up, but the
moment I changed anything it stopped. Where am I going wrong?

Given a 50-cell range $B$20:$B$44 and $B$56:$B$80 named as F_Shell.
Formula =COUNTIF(F_Shell,"0").
This returns #VALUE, but it returns the correct number if I use the cell
references.
I've tried entering it as a CSE: no change.
I've tried the suggestion from other questions in this forum to ensure it is
not text format, but general + double-click + enter: no change.

I'd appreciate any pointers.

Are you saying that you can use COUNTIF with a non-contiguous range if you
use the actual cell references. If so, I would like to see your formula
because I cannot get that to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jonathan" wrote in message
...
I'm using a simple Countif in XL2003 so don't need to CSE it. It fights
with
a named range and I cannot see why. It worked when I set it up, but the
moment I changed anything it stopped. Where am I going wrong?

Given a 50-cell range $B$20:$B$44 and $B$56:$B$80 named as F_Shell.
Formula =COUNTIF(F_Shell,"0").
This returns #VALUE, but it returns the correct number if I use the cell
references.
I've tried entering it as a CSE: no change.
I've tried the suggestion from other questions in this forum to ensure it
is
not text format, but general + double-click + enter: no change.

I'd appreciate any pointers.

You were right to doubt me--I had used just a simplified contiguous version
of the range in my test. It was not until I used the full non-contiguous
version that the range-naming died on me. Since COUNTIF is a specialised
array/CSE function, does this mean that such a function won't work if the
array is not contiguous(whatever the range might be named) i.e. it is not an
array?

"Bob Phillips" wrote:

Are you saying that you can use COUNTIF with a non-contiguous range if you
use the actual cell references. If so, I would like to see your formula
because I cannot get that to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jonathan" wrote in message
...
I'm using a simple Countif in XL2003 so don't need to CSE it. It fights
with
a named range and I cannot see why. It worked when I set it up, but the
moment I changed anything it stopped. Where am I going wrong?

Given a 50-cell range $B$20:$B$44 and $B$56:$B$80 named as F_Shell.
Formula =COUNTIF(F_Shell,"0").
This returns #VALUE, but it returns the correct number if I use the cell
references.
I've tried entering it as a CSE: no change.
I've tried the suggestion from other questions in this forum to ensure it
is
not text format, but general + double-click + enter: no change.

I'd appreciate any pointers.

COUNTIF is a function that processes a range of values, but it is not CSE,
and it cannot handle non-contiguous ranges, named or not. You need a
different approach.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jonathan" wrote in message
...
You were right to doubt me--I had used just a simplified contiguous
version
of the range in my test. It was not until I used the full non-contiguous
version that the range-naming died on me. Since COUNTIF is a specialised
array/CSE function, does this mean that such a function won't work if the
array is not contiguous(whatever the range might be named) i.e. it is not
an
array?

"Bob Phillips" wrote:

Are you saying that you can use COUNTIF with a non-contiguous range if
you
use the actual cell references. If so, I would like to see your formula
because I cannot get that to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jonathan" wrote in message
...
I'm using a simple Countif in XL2003 so don't need to CSE it. It
fights
with
a named range and I cannot see why. It worked when I set it up, but
the
moment I changed anything it stopped. Where am I going wrong?

Given a 50-cell range $B$20:$B$44 and $B$56:$B$80 named as F_Shell.
Formula =COUNTIF(F_Shell,"0").
This returns #VALUE, but it returns the correct number if I use the
cell
references.
I've tried entering it as a CSE: no change.
I've tried the suggestion from other questions in this forum to ensure
it
is
not text format, but general + double-click + enter: no change.

I'd appreciate any pointers.

Thanks for this help Bob, and for your very fast responses. I will go and
de-discontiguise my tables!

"Bob Phillips" wrote:

COUNTIF is a function that processes a range of values, but it is not CSE,
and it cannot handle non-contiguous ranges, named or not. You need a
different approach.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jonathan" wrote in message
...
You were right to doubt me--I had used just a simplified contiguous
version
of the range in my test. It was not until I used the full non-contiguous
version that the range-naming died on me. Since COUNTIF is a specialised
array/CSE function, does this mean that such a function won't work if the
array is not contiguous(whatever the range might be named) i.e. it is not
an
array?

"Bob Phillips" wrote:

Are you saying that you can use COUNTIF with a non-contiguous range if
you
use the actual cell references. If so, I would like to see your formula
because I cannot get that to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jonathan" wrote in message
...
I'm using a simple Countif in XL2003 so don't need to CSE it. It
fights
with
a named range and I cannot see why. It worked when I set it up, but
the
moment I changed anything it stopped. Where am I going wrong?

Given a 50-cell range $B$20:$B$44 and $B$56:$B$80 named as F_Shell.
Formula =COUNTIF(F_Shell,"0").
This returns #VALUE, but it returns the correct number if I use the
cell
references.
I've tried entering it as a CSE: no change.
I've tried the suggestion from other questions in this forum to ensure
it
is
not text format, but general + double-click + enter: no change.

I'd appreciate any pointers.

You can do it with non-contiguous ranges, but not the way you tried. For
instance

=SUMPRODUCT(COUNTIF(INDIRECT({"B20:B44","B56:B80"} ),"0"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jonathan" wrote in message
...
Thanks for this help Bob, and for your very fast responses. I will go and
de-discontiguise my tables!

"Bob Phillips" wrote:

COUNTIF is a function that processes a range of values, but it is not
CSE,
and it cannot handle non-contiguous ranges, named or not. You need a
different approach.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jonathan" wrote in message
...
You were right to doubt me--I had used just a simplified contiguous
version
of the range in my test. It was not until I used the full
non-contiguous
version that the range-naming died on me. Since COUNTIF is a
specialised
array/CSE function, does this mean that such a function won't work if
the
array is not contiguous(whatever the range might be named) i.e. it is
not
an
array?

"Bob Phillips" wrote:

Are you saying that you can use COUNTIF with a non-contiguous range
if
you
use the actual cell references. If so, I would like to see your
formula
because I cannot get that to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jonathan" wrote in message
...
I'm using a simple Countif in XL2003 so don't need to CSE it. It
fights
with
a named range and I cannot see why. It worked when I set it up,
but
the
moment I changed anything it stopped. Where am I going wrong?

Given a 50-cell range $B$20:$B$44 and $B$56:$B$80 named as
F_Shell.
Formula =COUNTIF(F_Shell,"0").
This returns #VALUE, but it returns the correct number if I use
the
cell
references.
I've tried entering it as a CSE: no change.
I've tried the suggestion from other questions in this forum to
ensure
it
is
not text format, but general + double-click + enter: no change.

I'd appreciate any pointers.