if then formula help
 

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!

if then formula help
 

=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)



"dsrambis" wrote in message
...
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!

if then formula help
 

Try =sumif() or =countif()

"dsrambis" wrote:

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!

if then formula help
 

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" wrote:

=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)



"dsrambis" wrote in message
...
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!

if then formula help
 

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" wrote in message
...
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" wrote:

=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)



"dsrambis" wrote in message
...
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!

if then formula help
 

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

"Bob Phillips" wrote:

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" wrote in message
...
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" wrote:

=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)



"dsrambis" wrote in message
...
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!

if then formula help
 

Look up absolute and relative addressing in Excel help.
--
David Biddulph

"dsrambis" wrote in message
...
Sorry to be such a bother...but where do I put the $ ??

"Bob Phillips" wrote:

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" wrote in message
...
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" wrote:

=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)



"dsrambis" wrote in message
...
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!

if then formula help
 

=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)



"dsrambis" wrote in message
...
Sorry to be such a bother...but where do I put the $ ??

"Bob Phillips" wrote:

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" wrote in message
...
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" wrote:

=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)



"dsrambis" wrote in message
...
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!