| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitution
When defining a range can you use a reference instead of the actual value?
countif(m6:m429,"ION") Can I replace the 429 with a reference to a cell containing the 429? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Substitution
=countif(indirect("m6:M" & a1),"ION")
or =countif(m6:indirect("M" & a1),"ION") In the second one, the m6 will adjust if you copy the formula. Mitchell wrote: When defining a range can you use a reference instead of the actual value? countif(m6:m429,"ION") Can I replace the 429 with a reference to a cell containing the 429? -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Substitution
I'm getting an evaluation error.
cell A42 contains the number 429 countif(Tasks!M6:indirect("M" & A42),"ION") countif(Tasks!M6:indirect("M" & 429), "ION") countif(Tasks!M^:indirect("M429"),"ION") countif(Tasks!$M$6:$M$429,"ION") The next evaluation will result in an error. It looks correct???? "Dave Peterson" wrote: =countif(indirect("m6:M" & a1),"ION") or =countif(m6:indirect("M" & a1),"ION") In the second one, the m6 will adjust if you copy the formula. Mitchell wrote: When defining a range can you use a reference instead of the actual value? countif(m6:m429,"ION") Can I replace the 429 with a reference to a cell containing the 429? -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Substitution
=countif(indirect("Tasks!M6:M" & A42),"ION")
or =countif(Tasks!M6:indirect("tasks!M" & A42),"ION") It would have been better to include the Tasks! stuff on your first post <bg. Mitchell wrote: I'm getting an evaluation error. cell A42 contains the number 429 countif(Tasks!M6:indirect("M" & A42),"ION") countif(Tasks!M6:indirect("M" & 429), "ION") countif(Tasks!M^:indirect("M429"),"ION") countif(Tasks!$M$6:$M$429,"ION") The next evaluation will result in an error. It looks correct???? "Dave Peterson" wrote: =countif(indirect("m6:M" & a1),"ION") or =countif(m6:indirect("M" & a1),"ION") In the second one, the m6 will adjust if you copy the formula. Mitchell wrote: When defining a range can you use a reference instead of the actual value? countif(m6:m429,"ION") Can I replace the 429 with a reference to a cell containing the 429? -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Substitution
I did some experimentation and found that the 1st solution listed worked, the
second one did not. "Mitchell" wrote: I'm getting an evaluation error. cell A42 contains the number 429 countif(Tasks!M6:indirect("M" & A42),"ION") countif(Tasks!M6:indirect("M" & 429), "ION") countif(Tasks!M^:indirect("M429"),"ION") countif(Tasks!$M$6:$M$429,"ION") The next evaluation will result in an error. It looks correct???? "Dave Peterson" wrote: =countif(indirect("m6:M" & a1),"ION") or =countif(m6:indirect("M" & a1),"ION") In the second one, the m6 will adjust if you copy the formula. Mitchell wrote: When defining a range can you use a reference instead of the actual value? countif(m6:m429,"ION") Can I replace the 429 with a reference to a cell containing the 429? -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Parameter Query Cell substitution | Excel Discussion (Misc queries) | |||
| Character Substitution in Array | Excel Discussion (Misc queries) | |||
| substitution | Excel Discussion (Misc queries) | |||
| Substitution | Excel Discussion (Misc queries) | |||
| why does ON = Ambiguous in substitution formula? | Excel Worksheet Functions | |||
Hybrid Mode
