countif across worksheets
 

I am trying to automate ta summary page of a quesionnaire process. The
questionnaire contains 6 worksheets. 5 of them are the questionnaires and
the 6th is the summary sheet. On the questionnaires there are 3 columns:
yes, no and n/a. On the summary sheet I need to count the values in the yes
column and turn that into a percentage. The value placed in the field is an
"X", and the top score would be 100% (5/5).

At this point I am trying just to count the X's in the yes column. I am
trying to use countif and listing the range as the cell in each worksheet,
separated by a comma, then "X". It is not working and gives me an error -
can't figure out why, nor how to fix it.

Does anyone have any suggestions? Thanks in advance for your help.

I think that you will have to add a separate sumif for each sheet

"MCK" wrote:

I am trying to automate ta summary page of a quesionnaire process. The
questionnaire contains 6 worksheets. 5 of them are the questionnaires and
the 6th is the summary sheet. On the questionnaires there are 3 columns:
yes, no and n/a. On the summary sheet I need to count the values in the yes
column and turn that into a percentage. The value placed in the field is an
"X", and the top score would be 100% (5/5).

At this point I am trying just to count the X's in the yes column. I am
trying to use countif and listing the range as the cell in each worksheet,
separated by a comma, then "X". It is not working and gives me an error -
can't figure out why, nor how to fix it.

Does anyone have any suggestions? Thanks in advance for your help.

I'm not sure I understand. Can you explain what you mean, please? Thanks.

"bj" wrote:

I think that you will have to add a separate sumif for each sheet

"MCK" wrote:

I am trying to automate ta summary page of a quesionnaire process. The
questionnaire contains 6 worksheets. 5 of them are the questionnaires and
the 6th is the summary sheet. On the questionnaires there are 3 columns:
yes, no and n/a. On the summary sheet I need to count the values in the yes
column and turn that into a percentage. The value placed in the field is an
"X", and the top score would be 100% (5/5).

At this point I am trying just to count the X's in the yes column. I am
trying to use countif and listing the range as the cell in each worksheet,
separated by a comma, then "X". It is not working and gives me an error -
can't figure out why, nor how to fix it.

Does anyone have any suggestions? Thanks in advance for your help.

There may be an easier way, but this will work.

=(COUNTIF(Sheet1!A:A,"x")+(COUNTIF(Sheet2!A:A,"x") +(COUNTIF(Sheet3!A:A,"x")+(COUNTIF(Sheet4!A:A,"x") +(COUNTIF(Sheet5!A:A,"x"))))))

A:A is your yes column.

HTH,
Paul

"MCK" wrote in message
...
I am trying to automate ta summary page of a quesionnaire process. The
questionnaire contains 6 worksheets. 5 of them are the questionnaires and
the 6th is the summary sheet. On the questionnaires there are 3 columns:
yes, no and n/a. On the summary sheet I need to count the values in the
yes
column and turn that into a percentage. The value placed in the field is
an
"X", and the top score would be 100% (5/5).

At this point I am trying just to count the X's in the yes column. I am
trying to use countif and listing the range as the cell in each worksheet,
separated by a comma, then "X". It is not working and gives me an error -
can't figure out why, nor how to fix it.

Does anyone have any suggestions? Thanks in advance for your help.

Try...

=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Sheet1","Sheet2 ","Sheet3","Sheet4","Sh
eet5"}&"'!A2:A10"),"x"))

or

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$D$1:$D$5&"'!A2:A 10"),"x"))

....where D1:D5 contains your sheet names. You can also use a whole
column reference, if you wish...

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$D$1:$D$5&"'!A:A" ),"x"))

Hope this helps!

In article ,
MCK wrote:

I am trying to automate ta summary page of a quesionnaire process. The
questionnaire contains 6 worksheets. 5 of them are the questionnaires and
the 6th is the summary sheet. On the questionnaires there are 3 columns:
yes, no and n/a. On the summary sheet I need to count the values in the yes
column and turn that into a percentage. The value placed in the field is an
"X", and the top score would be 100% (5/5).

At this point I am trying just to count the X's in the yes column. I am
trying to use countif and listing the range as the cell in each worksheet,
separated by a comma, then "X". It is not working and gives me an error -
can't figure out why, nor how to fix it.

Does anyone have any suggestions? Thanks in advance for your help.

One way


=SUMPRODUCT(COUNTIF(INDIRECT("'"&$Z$1:$Z$10&"'!A1" ),"Y"))where the criteria
is "Y", note that you need to put all sheet names in a range (in this
caseZ1:Z10 hold the sheet names) and the formula checks A1 in all the
sheets, you can us a range there as
well=SUMPRODUCT(COUNTIF(INDIRECT("'"&$Z$1:$Z$10&"' !A1:A10"),"Y"))
--
Regards,

Peo Sjoblom

(No private emails please)


"MCK" wrote in message
...
I'm not sure I understand. Can you explain what you mean, please?
Thanks.

"bj" wrote:

I think that you will have to add a separate sumif for each sheet

"MCK" wrote:

I am trying to automate ta summary page of a quesionnaire process. The
questionnaire contains 6 worksheets. 5 of them are the questionnaires
and
the 6th is the summary sheet. On the questionnaires there are 3
columns:
yes, no and n/a. On the summary sheet I need to count the values in
the yes
column and turn that into a percentage. The value placed in the field
is an
"X", and the top score would be 100% (5/5).

At this point I am trying just to count the X's in the yes column. I
am
trying to use countif and listing the range as the cell in each
worksheet,
separated by a comma, then "X". It is not working and gives me an
error -
can't figure out why, nor how to fix it.

Does anyone have any suggestions? Thanks in advance for your help.

Assuming that the yes answers are in E1:E100, try

=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Sheet1","Sheet2 ","Sheet3"","Sheet4","Shee
t5"}&"'!E1:E10"),"X"))

etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"MCK" wrote in message
...
I am trying to automate ta summary page of a quesionnaire process. The
questionnaire contains 6 worksheets. 5 of them are the questionnaires and
the 6th is the summary sheet. On the questionnaires there are 3 columns:
yes, no and n/a. On the summary sheet I need to count the values in the
yes
column and turn that into a percentage. The value placed in the field is
an
"X", and the top score would be 100% (5/5).

At this point I am trying just to count the X's in the yes column. I am
trying to use countif and listing the range as the cell in each worksheet,
separated by a comma, then "X". It is not working and gives me an error -
can't figure out why, nor how to fix it.

Does anyone have any suggestions? Thanks in advance for your help.

I tried this one, and did get this to work. Thank you all for your help.

"PCLIVE" wrote:

There may be an easier way, but this will work.

=(COUNTIF(Sheet1!A:A,"x")+(COUNTIF(Sheet2!A:A,"x") +(COUNTIF(Sheet3!A:A,"x")+(COUNTIF(Sheet4!A:A,"x") +(COUNTIF(Sheet5!A:A,"x"))))))

A:A is your yes column.

HTH,
Paul

"MCK" wrote in message
...
I am trying to automate ta summary page of a quesionnaire process. The
questionnaire contains 6 worksheets. 5 of them are the questionnaires and
the 6th is the summary sheet. On the questionnaires there are 3 columns:
yes, no and n/a. On the summary sheet I need to count the values in the
yes
column and turn that into a percentage. The value placed in the field is
an
"X", and the top score would be 100% (5/5).

At this point I am trying just to count the X's in the yes column. I am
trying to use countif and listing the range as the cell in each worksheet,
separated by a comma, then "X". It is not working and gives me an error -
can't figure out why, nor how to fix it.

Does anyone have any suggestions? Thanks in advance for your help.