Dynamic range creation for Countif statement

[elitebpoinfo]

Hi,

I am trying to dynamically create the range statement in Countif
formula but for some reason it doesn't work. At present I am hard
coding the information but I need it to dynamically create the range.

For Example:
There are two workbooks, one on which I am putting the Countif
statement and the other one is the reference.

The formula is used to determine the number of approved cases in west
coast like this:-
{=Countif('[WestCoast]211203to271203'!$A$2:$A$5000,"Closed") } equals
3000

But if I construct the range dynamically by referencing the cells, I
can construct the string statement but when used inside Countif it
doesn't work...I am doing like:
{=Countif(Concatenate("'[",A13,"Coast]",b13,"to",c13,"'!$A$2:$A$",$o$1),"$D$4)
} the formula doesn't work....



Please advise....



Rgds,

 

[Harlan Grove]

But if I construct the range dynamically by referencing the cells, I
can construct the string statement but when used inside Countif it
doesn't work...I am doing like:
{=Countif(Concatenate("'[",A13,"Coast]",b13,"to",c13,
"'!$A$2:$A$",$o$1),"$D$4)}
the formula doesn't work....

The formula doesn't work because the result of the CONCATENATE function is
just a string, no different than "'[foo.xls]bar'!$A$1:$A$999", which, as it
is, is *not* a range reference. See the INDIRECT function in online help.
You'll need to put your dynamic external link reference inside it. Even when
you've done so, it won't work if the file given by A13&"Coast" is closed.