Counting occurance of text values across multiple worksheets

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!

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
--
AP

"Jiq" <[email protected]> a écrit dans le message de news:
[email protected]...
> 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!

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

"Ardus Petus" wrote:

> 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
> --
> AP
>
> "Jiq" <[email protected]> a écrit dans le message de news:
> [email protected]...
> > 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!
>
>
>

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.

"Jiq" wrote:

> 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
>
> "Ardus Petus" wrote:
>
> > 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
> > --
> > AP
> >
> > "Jiq" <[email protected]> a écrit dans le message de news:
> > [email protected]...
> > > 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!
> >
> >
> >

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
--
AP


"Jiq" <[email protected]> a écrit dans le message de news:
[email protected]...
> 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.
>
> "Jiq" wrote:
>
>> 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
>>
>> "Ardus Petus" wrote:
>>
>> > 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
>> > --
>> > AP
>> >
>> > "Jiq" <[email protected]> a écrit dans le message de news:
>> > [email protected]...
>> > > 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!
>> >
>> >
>> >