SUMPRODUCT COUNTIF Problem

  • Thread starter Thread starter adrian007uk
  • Start date Start date
A

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!
 
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))
 
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
 
The formula i am using is:
=SUMPRODUCT(COUNTIF(INDIRECT(" "& Warehouses&"'D2"),"Pass"))
Click to expand...

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.
 
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"))+.....
 
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
 
Back
Top