Counting Number of Occurrences in worksheets

[Terri]

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.

 

[Jacob Skaria]

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

 

[Terri]

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.

 

[RagDyer]

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?

 

[Terri]

Yes, but will try again.

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?

 

[Jacob Skaria]

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

 

[Terri]

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.

 

[Terri]

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.