if then formula help

[dsrambis]

I have 96 students and want to determine how many students missed a
particular question (40 in all). I don't need to know the particular
students as I'm just trying to find out how many students missed a particular
question.

I have a spreadsheet which lists the question number in column A (i.e. 96
1's, 96 2's, 96 3's, etc) and in column B either a 0 (if the question were
missed) or a 1 (if the question was answered correctly)

I want a formula which will give me the number of students who missed
question 1, question 2, etc.

I've been trying to come up with a formula that will perform if column A
between cells A1 and A3840 is 1 (for question #1) then add all the zeros for
this same set (A1:A3840 if 1 and if 0).

This won't let me sleep...please help!

 

[Bob Phillips]

=COUNT(A:A)-COUNTIF(A:A,1)

will count those who missed Q1.

Alternative

=SUMPRODUCT(--(A1:A3840=1),--(B1:B3840=0))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Joel]

Try =sumif() or =countif()

 

[dsrambis]

Thank you...you are wonderful!!!

Is there anyway to copy the formula from cell to cell without having to
enter the formula 96 times? Right now, I've pasted the formula in 96 cells
and I'm going in to change the question number in each cell's formula.

I tried to drag the cross hairs as I would when copying a sequence but
couldn't get that to work with the formula.

 

[Bob Phillips]

Dragging the first should work okay, the second will need $ put in the cell
references.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[dsrambis]

Sorry to be such a bother...but where do I put the $ ??

 

[David Biddulph]

Look up absolute and relative addressing in Excel help.

 

[Bob Phillips]

=SUMPRODUCT(--($A$1:$A$3840=1),--($B$1:$B$3840=0))


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)