Countif + Named range

[Guest]

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.

 

[Bob Phillips]

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)

 

[Guest]

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]

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)

 

[Guest]

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

 

[Bob Phillips]

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)