The formula i am using is:
=SUMPRODUCT(COUNTIF(INDIRECT(" "& Warehouses&"'D2"),"Pass"))

Try it like this:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Warehouses&"'!D2" ),"Pass"))

Note that "'" is a double quote followed by a single quote followed by a
double quote.

--
Biff
Microsoft Excel MVP


"adrian007uk" wrote in message
...
I am having some problems getting an Excel function to work properly. I
have
tried this in Excel 2003 and 2007 so it is not the version that is the
problem.

I have a master sheet that is to be used to keep a tally of pass, fails
and
improvements that are required to specfic questions (e.g., 1 - 12). The
format of the master sheet is identical to the sheets that are used to
hold
data to the same 12 questions. Therefore there are 6 warehouses (sheets
2 -
7) dipicting the twelve questions that are displayed on master sheet.

What i am trying to do is count the number of pass, fails and improvements
(across the warehouses) for each question. To do this i have set the
master
sheet up with three colums (pass, fail and improvements). I am trying to
use
a SUMPRODUCT COUNTIF function but i just keep getting the #REF! error.

I have named the warehouses as a range in the master sheet (i.e., A2:A7)
called Warehouses.

The formula i am using is:

=SUMPRODUCT(COUNTIF(INDIRECT(" "& Warehouses&"'D2"),"Pass"))

I have gone through the formula evaluation tool and everything looks fine.
I have also tried to just use the range A2:A7 as opposed to 'Warehouses'.

Please help!