Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif and date formats | Excel Discussion (Misc queries) | |||
CountIf by Month-to-date | Excel Worksheet Functions | |||
countif date | Excel Worksheet Functions | |||
CountIf on Date field | Excel Worksheet Functions | |||
Using Countif for Date Matches | Excel Worksheet Functions |