I have a project that entails calculating the number of times an answer
appears on a range of worksheets and inserting the result on my compilation
worksheet in the same file. For example:

Question Yes No Other
Do you like Sushi? X

I need to calculate the total number of "X" in the Yes, No and Other columns
from many separate worksheets in the same file.

I am using Excel 2002.

Any assistance would be appreciated. Thanks.

With the sheetnames in range G1:G10; the below formula will return the count
of X in cell B2 in all sheets mentioned in the range G1:G10

=SUMPRODUCT(COUNTIF(INDIRECT("'"& G1:G10 &"'!b2"),"X"))

If this post helps click Yes
---------------
Jacob Skaria


"Terri" wrote:

I have a project that entails calculating the number of times an answer
appears on a range of worksheets and inserting the result on my compilation
worksheet in the same file. For example:

Question Yes No Other
Do you like Sushi? X

I need to calculate the total number of "X" in the Yes, No and Other columns
from many separate worksheets in the same file.

I am using Excel 2002.

Any assistance would be appreciated. Thanks.

Unfortunately, I receive a "#NAME?" error when I try this formula. I tried
it with and without spaces between the & and what follows. Not sure what I'm
doing wrong. Thanks.

"Jacob Skaria" wrote:

With the sheetnames in range G1:G10; the below formula will return the count
of X in cell B2 in all sheets mentioned in the range G1:G10

=SUMPRODUCT(COUNTIF(INDIRECT("'"& G1:G10 &"'!b2"),"X"))

If this post helps click Yes
---------------
Jacob Skaria


"Terri" wrote:

I have a project that entails calculating the number of times an answer
appears on a range of worksheets and inserting the result on my compilation
worksheet in the same file. For example:

Question Yes No Other
Do you like Sushi? X

I need to calculate the total number of "X" in the Yes, No and Other columns
from many separate worksheets in the same file.

I am using Excel 2002.

Any assistance would be appreciated. Thanks.

The only way I can see you getting a #Name? error is misspelling a function
or a cell reference, or if your actual formula is using ranges that may be
non-existent or misspelled.

Did you copy Jacob's formula directly into your sheet from his post?


--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Terri" wrote in message
...
Unfortunately, I receive a "#NAME?" error when I try this formula. I
tried
it with and without spaces between the & and what follows. Not sure what
I'm
doing wrong. Thanks.

"Jacob Skaria" wrote:

With the sheetnames in range G1:G10; the below formula will return the
count
of X in cell B2 in all sheets mentioned in the range G1:G10

=SUMPRODUCT(COUNTIF(INDIRECT("'"& G1:G10 &"'!b2"),"X"))

If this post helps click Yes
---------------
Jacob Skaria


"Terri" wrote:

I have a project that entails calculating the number of times an answer
appears on a range of worksheets and inserting the result on my
compilation
worksheet in the same file. For example:

Question Yes No
Other
Do you like Sushi? X

I need to calculate the total number of "X" in the Yes, No and Other
columns
from many separate worksheets in the same file.

I am using Excel 2002.

Any assistance would be appreciated. Thanks.

Yes, but will try again.

"RagDyer" wrote:

The only way I can see you getting a #Name? error is misspelling a function
or a cell reference, or if your actual formula is using ranges that may be
non-existent or misspelled.

Did you copy Jacob's formula directly into your sheet from his post?


--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Terri" wrote in message
...
Unfortunately, I receive a "#NAME?" error when I try this formula. I
tried
it with and without spaces between the & and what follows. Not sure what
I'm
doing wrong. Thanks.

"Jacob Skaria" wrote:

With the sheetnames in range G1:G10; the below formula will return the
count
of X in cell B2 in all sheets mentioned in the range G1:G10

=SUMPRODUCT(COUNTIF(INDIRECT("'"& G1:G10 &"'!b2"),"X"))

If this post helps click Yes
---------------
Jacob Skaria


"Terri" wrote:

I have a project that entails calculating the number of times an answer
appears on a range of worksheets and inserting the result on my
compilation
worksheet in the same file. For example:

Question Yes No
Other
Do you like Sushi? X

I need to calculate the total number of "X" in the Yes, No and Other
columns
from many separate worksheets in the same file.

I am using Excel 2002.

Any assistance would be appreciated. Thanks.

Hi Teri

G1:G10 range should not be blank or invalid. Try with 3 sheets..

=SUMPRODUCT(COUNTIF(INDIRECT("'"& G1:G3 &"'!b2"),"X"))

If this post helps click Yes
---------------
Jacob Skaria


"Terri" wrote:

Yes, but will try again.

"RagDyer" wrote:

The only way I can see you getting a #Name? error is misspelling a function
or a cell reference, or if your actual formula is using ranges that may be
non-existent or misspelled.

Did you copy Jacob's formula directly into your sheet from his post?


--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Terri" wrote in message
...
Unfortunately, I receive a "#NAME?" error when I try this formula. I
tried
it with and without spaces between the & and what follows. Not sure what
I'm
doing wrong. Thanks.

"Jacob Skaria" wrote:

With the sheetnames in range G1:G10; the below formula will return the
count
of X in cell B2 in all sheets mentioned in the range G1:G10

=SUMPRODUCT(COUNTIF(INDIRECT("'"& G1:G10 &"'!b2"),"X"))

If this post helps click Yes
---------------
Jacob Skaria


"Terri" wrote:

I have a project that entails calculating the number of times an answer
appears on a range of worksheets and inserting the result on my
compilation
worksheet in the same file. For example:

Question Yes No
Other
Do you like Sushi? X

I need to calculate the total number of "X" in the Yes, No and Other
columns
from many separate worksheets in the same file.

I am using Excel 2002.

Any assistance would be appreciated. Thanks.

Some of the cells will be blank as not every worksheet will have an "X" in
the Yes column, some will will an "X" in the Yes, No or other column. My
formula needs to include an individual count of the "X" in the Yes column,
the No column and the other column in subsequent worksheets (there may
eventually be up to 30 worksheets).

I even tried renaming my worksheets to R1, R2, etc. as my worksheets are not
that wide.

Thanks.

"Jacob Skaria" wrote:

Hi Teri

G1:G10 range should not be blank or invalid. Try with 3 sheets..

=SUMPRODUCT(COUNTIF(INDIRECT("'"& G1:G3 &"'!b2"),"X"))

If this post helps click Yes
---------------
Jacob Skaria


"Terri" wrote:

Yes, but will try again.

"RagDyer" wrote:

The only way I can see you getting a #Name? error is misspelling a function
or a cell reference, or if your actual formula is using ranges that may be
non-existent or misspelled.

Did you copy Jacob's formula directly into your sheet from his post?


--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Terri" wrote in message
...
Unfortunately, I receive a "#NAME?" error when I try this formula. I
tried
it with and without spaces between the & and what follows. Not sure what
I'm
doing wrong. Thanks.

"Jacob Skaria" wrote:

With the sheetnames in range G1:G10; the below formula will return the
count
of X in cell B2 in all sheets mentioned in the range G1:G10

=SUMPRODUCT(COUNTIF(INDIRECT("'"& G1:G10 &"'!b2"),"X"))

If this post helps click Yes
---------------
Jacob Skaria


"Terri" wrote:

I have a project that entails calculating the number of times an answer
appears on a range of worksheets and inserting the result on my
compilation
worksheet in the same file. For example:

Question Yes No
Other
Do you like Sushi? X

I need to calculate the total number of "X" in the Yes, No and Other
columns
from many separate worksheets in the same file.

I am using Excel 2002.

Any assistance would be appreciated. Thanks.

Thanks to all who responded.

With the assistance with my IT department, I have come up with
=COUNTA(G1!B2,G2!B2,G3!B2)[etc]

and so far it works!!

Thanks.

"Terri" wrote:

Some of the cells will be blank as not every worksheet will have an "X" in
the Yes column, some will will an "X" in the Yes, No or other column. My
formula needs to include an individual count of the "X" in the Yes column,
the No column and the other column in subsequent worksheets (there may
eventually be up to 30 worksheets).

I even tried renaming my worksheets to R1, R2, etc. as my worksheets are not
that wide.

Thanks.

"Jacob Skaria" wrote:

Hi Teri

G1:G10 range should not be blank or invalid. Try with 3 sheets..

=SUMPRODUCT(COUNTIF(INDIRECT("'"& G1:G3 &"'!b2"),"X"))

If this post helps click Yes
---------------
Jacob Skaria


"Terri" wrote:

Yes, but will try again.

"RagDyer" wrote:

The only way I can see you getting a #Name? error is misspelling a function
or a cell reference, or if your actual formula is using ranges that may be
non-existent or misspelled.

Did you copy Jacob's formula directly into your sheet from his post?


--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Terri" wrote in message
...
Unfortunately, I receive a "#NAME?" error when I try this formula. I
tried
it with and without spaces between the & and what follows. Not sure what
I'm
doing wrong. Thanks.

"Jacob Skaria" wrote:

With the sheetnames in range G1:G10; the below formula will return the
count
of X in cell B2 in all sheets mentioned in the range G1:G10

=SUMPRODUCT(COUNTIF(INDIRECT("'"& G1:G10 &"'!b2"),"X"))

If this post helps click Yes
---------------
Jacob Skaria


"Terri" wrote:

I have a project that entails calculating the number of times an answer
appears on a range of worksheets and inserting the result on my
compilation
worksheet in the same file. For example:

Question Yes No
Other
Do you like Sushi? X

I need to calculate the total number of "X" in the Yes, No and Other
columns
from many separate worksheets in the same file.

I am using Excel 2002.

Any assistance would be appreciated. Thanks.