SUMPRODUCT COUNTIF Problem

[adrian007uk]

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!

 

[Eduardo]

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))

 

[Eduardo]

opps sorry I made a mistake use

=SUMPRODUCT((C1=A1:A7)*(B1:B7="Pass"))

 

[adrian007uk]

Hi Eduardo

The pass, fails etc will not be in colum B on the master sheet but acros the
7 worksheets. For example if question one was:

Deliveries were on time

Warehouse 1 (on sheet 2) may get pass, Warehouse 2 (on sheet 3) fail,
Warehouse 3 (on sheet 4) improvements, Warehouse 4 (on shet 5) pass etc.

Adrian

 

[T. Valko]

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.

 

[Eduardo]

Hi add the other sheets as follow

opps sorry I made a mistake use

=SUMPRODUCT((C1=A1:A7)*(B1:B7="Pass"))+SUMPRODUCT((C1=sheet2!A1:A7)*(sheet2!B1:B7="Pass"))+.....

 

[adrian007uk]

Hi Biff

Thank you so much. It was the missing ' between "" to make "'" that was
causing all the problems. I had mis-read the formula when i was doing my
research into solving my initial problem. I noticed people were using
SUMPRODUCT COUNTIF formulas but couldn't get it to work for me until now.

Many thanks.

Adrian

 

[T. Valko]

You're welcome. Thanks for the feedback!