I have a data sheet looking like this...

Unit Date Q1 Q2 Q3
2SE 7/2/08 Y Y Y
2SE 7/2/08 N Y Y
2W 7/2/08 Y Y N
4N 7/2/08 N Y Y

I want to set up a summary sheet that calculates the total of "Y" for each
question by Unit. Can someone help?

Thanks,
ml

With the Units in Column A of Sheet2 try:

=SUMPRODUCT((Sheet1!$A$2:$A$5=A2)*(Sheet1!$C$2:$C$ 5="Y"))

for Q1. Adjust as required for Q2 etc.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"ml" wrote in message
...
I have a data sheet looking like this...

Unit Date Q1 Q2 Q3
2SE 7/2/08 Y Y Y
2SE 7/2/08 N Y Y
2W 7/2/08 Y Y N
4N 7/2/08 N Y Y

I want to set up a summary sheet that calculates the total of "Y" for each
question by Unit. Can someone help?

Thanks,
ml

Thank you so much for your help! I have one more question...

Unit Date Q1 Q2 Q3 Q4
2SE 7/2/08 Y Y Y Y
2SE 7/2/08 N Y Y
2W 7/2/08 Y Y N N
4N 7/2/08 N Y Y

If I want to calculate % of Y for Q4 using your suggested formula. Using
"2" as my denominator (since I only have 2 answers), how do I do that?

Thank you a bunch!
ml




"Sandy Mann" wrote:

With the Units in Column A of Sheet2 try:

=SUMPRODUCT((Sheet1!$A$2:$A$5=A2)*(Sheet1!$C$2:$C$ 5="Y"))

for Q1. Adjust as required for Q2 etc.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"ml" wrote in message
...
I have a data sheet looking like this...

Unit Date Q1 Q2 Q3
2SE 7/2/08 Y Y Y
2SE 7/2/08 N Y Y
2W 7/2/08 Y Y N
4N 7/2/08 N Y Y

I want to set up a summary sheet that calculates the total of "Y" for each
question by Unit. Can someone help?

Thanks,
ml

Try:

=SUMPRODUCT((Sheet1!$A$2:$A$5=A2)*(Sheet1!$C$2:$C$ 5="Y"))/COUNTIF(Sheet1!$A$2:$A$5,A2)

with the cell formatted as Percentage. This will calculate the percentage
regardless of how many answers there are. Note however that it ignores the
date.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"ml" wrote in message
...
Thank you so much for your help! I have one more question...

Unit Date Q1 Q2 Q3 Q4
2SE 7/2/08 Y Y Y Y
2SE 7/2/08 N Y Y
2W 7/2/08 Y Y N N
4N 7/2/08 N Y Y

If I want to calculate % of Y for Q4 using your suggested formula. Using
"2" as my denominator (since I only have 2 answers), how do I do that?

Thank you a bunch!
ml




"Sandy Mann" wrote:

With the Units in Column A of Sheet2 try:

=SUMPRODUCT((Sheet1!$A$2:$A$5=A2)*(Sheet1!$C$2:$C$ 5="Y"))

for Q1. Adjust as required for Q2 etc.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"ml" wrote in message
...
I have a data sheet looking like this...

Unit Date Q1 Q2 Q3
2SE 7/2/08 Y Y Y
2SE 7/2/08 N Y Y
2W 7/2/08 Y Y N
4N 7/2/08 N Y Y

I want to set up a summary sheet that calculates the total of "Y" for
each
question by Unit. Can someone help?

Thanks,
ml