Counting Number of Occurrences in worksheets

T

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.
 
J

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
 
T

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.
 
R

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?
 
T

Terri

Yes, but will try again.

RagDyer said:
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?
 
J

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
 
T

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.
 
T

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top