Hi,
if you have the name of the warehouse in column A, and the word Pass in
column B, and you enter the warehouse you want to count in Cell C1 use

=sumproduct((C1=A1:A7),(B1:B7="Pass))

"adrian007uk" wrote:

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!