can't use function 'countif' in named range

[Roland]

have a named range from non-adjacent ranges
most of Excels build-in functions can be used with this named range (such
as, min, max are sum)
However the function CountIf can't be used,
got a message in the cell "#value!"
why is that ?

 

[Gary''s Student]

You can't use COUNTIF() in disjoint ranges. Use:

=COUNTIF()+COUNTIF()+...

instead

 

[Roland]

agree
but the named range is dynamically, meaning it expands on regular base, this
mean I have to change the formula each time !

 

[John C]

There might be other methods of doing your counts. Are your named ranges
broken up with regularity? What defines a named range? Is it a particular
column, cell, calculation?

 

[rolando]

no regularity in ranges. ranges will be decrease or increase on regular base.
ranges are defined in 1 single column
for example; range name is defined as cells B1:B5 + B8:B10 + B15
only last cell, B15, can be extended or new ranges can follow further down
regards
roland

 

[John C]

What I am trying to ask, is, why are cells B1:B5 a named range? why are cells
B8:B10 a named range, and B15+ a named range? What makes them named? What
criteria are you using to name these ranges?

 

[Roland]

dear John
in the same workbook, in different sheets, the same ranges (the respective
named ranges) are used for different aims, e.g. making averages, graphs,...
when the ranges changes in size, its easier to change only the name instead
of changing all the different formulas in respective sheets/cells or graphs
r

 

[John C]

Let me re-phrase the question. Let's say you have named ranges as given. And
I understand using named ranges for charts, I've done it myself. But what
criteria are you using when you select the named range, or decide to change
the range name, or ...? Are you just choosing 3 random cells here, 2 random
cells there, and 4 more over here, etc? Or are you, for example, saying OK, I
want B1:B5 to be named Range1 because the values in A1:A5 are currently
matching the criteria I wish to chart, and Range2 is B8:B10 because A8:A10
currently matches the criteria I wish to chart. The criteria could be a
specific result, a date, ID#s, a variance that is within a +/- of whatever
tolerance, and so forth. I have to assume that these non-adjacent ranges have
to have something in common, or else you wouldn't want to do a countif on all
the ranges. (An example at my previous employer, someone wanted to count the
total # of items sold across a spread of about 32 items. However, some things
are bought in bulk, and some are large units that a customer would only buy 1
of, having a total for EACH type of item, sure, but adding them together is a
nonsensical number).

 

[rolando]

John
1/ specific named ranges are always in the same column
2/ all cells within the named ranges contain values from a certain valid
experimental study
3/ these values are independent from each other with no correlation or
relation, or mayching criteria watsoever, meaning can be any value; the
decision to in/ex-clude this cell in the named range depends of the validity
of the study
hope this is more clear
roland