| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
can't use function 'countif' in named range
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 ? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
can't use function 'countif' in named range
You can't use COUNTIF() in disjoint ranges. Use:
=COUNTIF()+COUNTIF()+... instead -- Gary''s Student - gsnu200811 "Roland" wrote: 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 ? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
can't use function 'countif' in named range
agree
but the named range is dynamically, meaning it expands on regular base, this mean I have to change the formula each time ! "Gary''s Student" wrote: You can't use COUNTIF() in disjoint ranges. Use: =COUNTIF()+COUNTIF()+... instead -- Gary''s Student - gsnu200811 "Roland" wrote: 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 ? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
can't use function 'countif' in named range
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? -- ** John C ** "Roland" wrote: agree but the named range is dynamically, meaning it expands on regular base, this mean I have to change the formula each time ! "Gary''s Student" wrote: You can't use COUNTIF() in disjoint ranges. Use: =COUNTIF()+COUNTIF()+... instead -- Gary''s Student - gsnu200811 "Roland" wrote: 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 ? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
can't use function 'countif' in named range
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" wrote: 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? -- ** John C ** "Roland" wrote: agree but the named range is dynamically, meaning it expands on regular base, this mean I have to change the formula each time ! "Gary''s Student" wrote: You can't use COUNTIF() in disjoint ranges. Use: =COUNTIF()+COUNTIF()+... instead -- Gary''s Student - gsnu200811 "Roland" wrote: 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 ? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
can't use function 'countif' in named range
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? -- ** John C ** "rolando" wrote: 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" wrote: 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? -- ** John C ** "Roland" wrote: agree but the named range is dynamically, meaning it expands on regular base, this mean I have to change the formula each time ! "Gary''s Student" wrote: You can't use COUNTIF() in disjoint ranges. Use: =COUNTIF()+COUNTIF()+... instead -- Gary''s Student - gsnu200811 "Roland" wrote: 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 ? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
can't use function 'countif' in named range
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" wrote: 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? -- ** John C ** "rolando" wrote: 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" wrote: 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? -- ** John C ** "Roland" wrote: agree but the named range is dynamically, meaning it expands on regular base, this mean I have to change the formula each time ! "Gary''s Student" wrote: You can't use COUNTIF() in disjoint ranges. Use: =COUNTIF()+COUNTIF()+... instead -- Gary''s Student - gsnu200811 "Roland" wrote: 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 ? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
can't use function 'countif' in named range
You could use a UDF that loops through the areas, analogous to what Roland suggested. -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24858 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
can't use function 'countif' in named range
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). -- ** John C ** "Roland" wrote: 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" wrote: 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? -- ** John C ** "rolando" wrote: 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" wrote: 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? -- ** John C ** "Roland" wrote: agree but the named range is dynamically, meaning it expands on regular base, this mean I have to change the formula each time ! "Gary''s Student" wrote: You can't use COUNTIF() in disjoint ranges. Use: =COUNTIF()+COUNTIF()+... instead -- Gary''s Student - gsnu200811 "Roland" wrote: 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 ? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
can't use function 'countif' in named range
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 "John C" wrote: 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). -- ** John C ** "Roland" wrote: 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" wrote: 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? -- ** John C ** "rolando" wrote: 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" wrote: 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? -- ** John C ** "Roland" wrote: agree but the named range is dynamically, meaning it expands on regular base, this mean I have to change the formula each time ! "Gary''s Student" wrote: You can't use COUNTIF() in disjoint ranges. Use: =COUNTIF()+COUNTIF()+... instead -- Gary''s Student - gsnu200811 "Roland" wrote: 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 ? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Variable named range in worksheet function | Excel Worksheet Functions | |||
| Named Range references in function formulas | Excel Worksheet Functions | |||
| Countif + Named range | Excel Worksheet Functions | |||
| dynamic named range function | Excel Worksheet Functions | |||
| how do you use named cells in the countif function | Excel Worksheet Functions | |||
Linear Mode
