SUMPRODUCT COUNTIF Problem
 

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!

SUMPRODUCT COUNTIF Problem
 

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!

SUMPRODUCT COUNTIF Problem
 

opps sorry I made a mistake use

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

"Eduardo" wrote:

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!

SUMPRODUCT COUNTIF Problem
 

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

"Eduardo" wrote:

opps sorry I made a mistake use

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

"Eduardo" wrote:

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!

SUMPRODUCT COUNTIF Problem
 

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!

SUMPRODUCT COUNTIF Problem
 

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" wrote:

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

"Eduardo" wrote:

opps sorry I made a mistake use

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

"Eduardo" wrote:

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!

SUMPRODUCT COUNTIF Problem
 

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" wrote:

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!


.

SUMPRODUCT COUNTIF Problem
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"adrian007uk" wrote in message
...
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" wrote:

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!


.