Counting occurance of text values across multiple worksheets

[Guest]

Sorry for any repetition with previous questions but I am completely hopeless
with excel functions and got lost when attempting to use functions contained
within previous postings.

My problem: I have a questionnaire for 27 sites. For each site I have the
completed questionnaire entered into a seperate worksheet i.e 27 worksheets.
The answer for each question is "Y", "N", "NK", or "NA". I need to provide a
summary of the 27 sites which I want to enter on a blank master questionnaire
on a seperate sheet. Can anyone help with a formula that counts the number of
"Y", etc for each question (the cell for each question is the same for each
site on the different sheets). If possible, can the summary cell indicate the
number of Y's, the number of N's, the number of NK's, and the number of
NA's?? It would also be fantastic to produce the results for each Y, N, NK,
and NA as a %.

Each questionnaire contains approx 200 questions so I would like to be able
to paste the formula into each result cell on the summary sheet.

Any assistance gratefully received!

 

[Ardus Petus]

I found a solution: linking the answer values to columns of summary sheet.
Then you can perform calculations within summary sheet.
Answer columns may be hidden in summary sheet.

See example with 3 sites and 16 questions: http://cjoint.com/?ftmPAplI7b

HTH

 

[Guest]

Thanks Ardus,
I will give it a try in a bit. If it works like your example I will be
totally grateful!
May need additional help as I attempt it though as when I looked at the
formulas you used I got lost!
Thanks for your help.
Jiq

 

[Guest]

Help please Ardus.
I tried to enter the formulas that you used in your example but I don't know
how to link the answer values to columns of the summary sheet. I don't
understand the formula used. Could you explain for me so that I can re-enter?
Many thanks
Jiq.

 

[Ardus Petus]

It is the same formula from F2 to H17. Excel automatically moves the
relative references to produce different references.

For F2, the formula appears as:
=INDIRECT(F$1&"!B"&ROW())

INDIRECT function has 1 string parameter, which must be a cell(s) reference.
For instance: =INDIRECT("B12") produces the same result as =B12

F$1 resolves to the first cell of current column, where you find the name of
the data worksheet ("Feuil2")

ROW() returns current row (2)

F$1&"!B"&ROW() concatenates the 3 elements and returns string "Feuil2!B2",
which is the reference of the cell containing the answer to question #1

I used INDIRECT and ROW functions so that I can use the same formula
throughout the whole array.

I hope I have beeen clear enough

You must replace my sheet names (feuil2, feuil3, feuil4) by the names of
your individual site worksheets
(if you have more than 3, you can extend the list to the right, then copy my
formula to the newly created columns).

If you're stuck, you can upload your workbook on http://cjoint.com , post
back the link, and I'll be glad to fix it for you.

HTH