Countif only col a = col b by date
 

I am trying to count the number of times both col a and col b have a 1 in
them on the same day. The spreadsheet has three columns A=Date C=1 or
blank,D=1or blank. The formula I have is this
=COUNTIF(C1:C367,"1")-COUNTIF(D1:D367,"1") but it is not giving me the
correct count number of times that column c and col d are equal. Any
suggestions?

Countif only col a = col b by date
 

try
=sumproduct(--(c1:c367=d1:d367))
the"--(" makes the logical true false be a numeric 1 0


"RichardM" wrote:

I am trying to count the number of times both col a and col b have a 1 in
them on the same day. The spreadsheet has three columns A=Date C=1 or
blank,D=1or blank. The formula I have is this
=COUNTIF(C1:C367,"1")-COUNTIF(D1:D367,"1") but it is not giving me the
correct count number of times that column c and col d are equal. Any
suggestions?

Countif only col a = col b by date
 

=SUMPRODUCT(--(A1:A100=--"3/17/2007"),--(C1:C100=1),--(D1:D100=1))

Adjust to suit


"RichardM" wrote:

I am trying to count the number of times both col a and col b have a 1 in
them on the same day. The spreadsheet has three columns A=Date C=1 or
blank,D=1or blank. The formula I have is this
=COUNTIF(C1:C367,"1")-COUNTIF(D1:D367,"1") but it is not giving me the
correct count number of times that column c and col d are equal. Any
suggestions?

Countif only col a = col b by date
 

Thank you for the formula, however I think I haven't explained the question
right. There are 364 days in the year. I am trying to count how many times
col c equals col d. Is it 2 or 6 or 360?

"bj" wrote:

try
=sumproduct(--(c1:c367=d1:d367))
the"--(" makes the logical true false be a numeric 1 0


"RichardM" wrote:

I am trying to count the number of times both col a and col b have a 1 in
them on the same day. The spreadsheet has three columns A=Date C=1 or
blank,D=1or blank. The formula I have is this
=COUNTIF(C1:C367,"1")-COUNTIF(D1:D367,"1") but it is not giving me the
correct count number of times that column c and col d are equal. Any
suggestions?

Countif only col a = col b by date
 

try this to check to see if data in col C
=SUMPRODUCT(--(C1:C367<""),--(C1:C367=D1:D367))

--
Don Guillett
SalesAid Software

"RichardM" wrote in message
...
Thank you for the formula, however I think I haven't explained the
question
right. There are 364 days in the year. I am trying to count how many times
col c equals col d. Is it 2 or 6 or 360?

"bj" wrote:

try
=sumproduct(--(c1:c367=d1:d367))
the"--(" makes the logical true false be a numeric 1 0


"RichardM" wrote:

I am trying to count the number of times both col a and col b have a 1
in
them on the same day. The spreadsheet has three columns A=Date C=1 or
blank,D=1or blank. The formula I have is this
=COUNTIF(C1:C367,"1")-COUNTIF(D1:D367,"1") but it is not giving me the
correct count number of times that column c and col d are equal. Any
suggestions?

Countif only col a = col b by date
 

I misread your question, try
=sumproduct(--(c1:c367=1),--(d1:d367=1))

"RichardM" wrote:

Thank you for the formula, however I think I haven't explained the question
right. There are 364 days in the year. I am trying to count how many times
col c equals col d. Is it 2 or 6 or 360?

"bj" wrote:

try
=sumproduct(--(c1:c367=d1:d367))
the"--(" makes the logical true false be a numeric 1 0


"RichardM" wrote:

I am trying to count the number of times both col a and col b have a 1 in
them on the same day. The spreadsheet has three columns A=Date C=1 or
blank,D=1or blank. The formula I have is this
=COUNTIF(C1:C367,"1")-COUNTIF(D1:D367,"1") but it is not giving me the
correct count number of times that column c and col d are equal. Any
suggestions?