Summary help!

M

ml

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
 
S

Sandy Mann

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
M

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
 
S

Sandy Mann

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top