countif across worksheets

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[PCLIVE]

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

 

[Domenic]

Try...

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

or

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

....where D15 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!

 

[Peo Sjoblom]

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)

 

[Bob Phillips]

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)

 

[Guest]

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