| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif function using dates
I want to calculate the number of entries in a column that are later than one
date and earlier than another. This is what I thought would work, but doesn't: =COUNTIF(M3:M158,"30/09/2007"&"<01/11/2007") |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif function using dates
Try it this way:
=COUNTIF(M3:M158,"30/09/2007")- COUNTIF(M3:M158,"=01/11/2007") Hope this helps. Pete On Oct 12, 10:20 pm, Cassidy1 wrote: I want to calculate the number of entries in a column that are later than one date and earlier than another. This is what I thought would work, but doesn't: =COUNTIF(M3:M158,"30/09/2007"&"<01/11/2007") |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif function using dates
=SUMPRODUCT(--(MONTH(M3:M158)=10))
"Cassidy1" wrote: I want to calculate the number of entries in a column that are later than one date and earlier than another. This is what I thought would work, but doesn't: =COUNTIF(M3:M158,"30/09/2007"&"<01/11/2007") |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif function using dates
Will not work if the month is January and if there happens to be some blank
cells included. -- Regards, Peo Sjoblom "Teethless mama" wrote in message ... =SUMPRODUCT(--(MONTH(M3:M158)=10)) "Cassidy1" wrote: I want to calculate the number of entries in a column that are later than one date and earlier than another. This is what I thought would work, but doesn't: =COUNTIF(M3:M158,"30/09/2007"&"<01/11/2007") |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif function using dates
You're absolutely correct. For the month of January must have year included
=SUMPRODUCT(--(MONTH(M3:M158)=1),--(YEAR(M3:M158)=2007) "Peo Sjoblom" wrote: Will not work if the month is January and if there happens to be some blank cells included. -- Regards, Peo Sjoblom "Teethless mama" wrote in message ... =SUMPRODUCT(--(MONTH(M3:M158)=10)) "Cassidy1" wrote: I want to calculate the number of entries in a column that are later than one date and earlier than another. This is what I thought would work, but doesn't: =COUNTIF(M3:M158,"30/09/2007"&"<01/11/2007") |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif function using dates
=SUM(COUNTIF(M3:M158,{"2007/9/30","2007/10/31"})*{1,-1})
"Teethless mama" wrote: You're absolutely correct. For the month of January must have year included =SUMPRODUCT(--(MONTH(M3:M158)=1),--(YEAR(M3:M158)=2007) "Peo Sjoblom" wrote: Will not work if the month is January and if there happens to be some blank cells included. -- Regards, Peo Sjoblom "Teethless mama" wrote in message ... =SUMPRODUCT(--(MONTH(M3:M158)=10)) "Cassidy1" wrote: I want to calculate the number of entries in a column that are later than one date and earlier than another. This is what I thought would work, but doesn't: =COUNTIF(M3:M158,"30/09/2007"&"<01/11/2007") |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif function using dates
=SUM(COUNTIF(M3:M158,{"30/09/2007","31/10/2007"})*{1,-1})
Revised for the date format And, for the month of January : =SUMPRODUCT(--(TEXT(M3:M45,"m/yyy")="1/2007")) "bpsco_yip" wrote: =SUM(COUNTIF(M3:M158,{"2007/9/30","2007/10/31"})*{1,-1}) "Teethless mama" wrote: You're absolutely correct. For the month of January must have year included =SUMPRODUCT(--(MONTH(M3:M158)=1),--(YEAR(M3:M158)=2007) "Peo Sjoblom" wrote: Will not work if the month is January and if there happens to be some blank cells included. -- Regards, Peo Sjoblom "Teethless mama" wrote in message ... =SUMPRODUCT(--(MONTH(M3:M158)=10)) "Cassidy1" wrote: I want to calculate the number of entries in a column that are later than one date and earlier than another. This is what I thought would work, but doesn't: =COUNTIF(M3:M158,"30/09/2007"&"<01/11/2007") |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif function using dates
It worked! Thanks very much Pete.
"Pete_UK" wrote: Try it this way: =COUNTIF(M3:M158,"30/09/2007")- COUNTIF(M3:M158,"=01/11/2007") Hope this helps. Pete On Oct 12, 10:20 pm, Cassidy1 wrote: I want to calculate the number of entries in a column that are later than one date and earlier than another. This is what I thought would work, but doesn't: =COUNTIF(M3:M158,"30/09/2007"&"<01/11/2007") |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Countif Function for dates in a range | Excel Worksheet Functions | |||
| A week of dates and counting them using the COUNTIF function with contingencies | Excel Worksheet Functions | |||
| CountIF with dates | Excel Discussion (Misc queries) | |||
| Need help on countif and sumif function with dates and wildcard characters | Excel Discussion (Misc queries) | |||
| COUNTIF using Dates | Excel Worksheet Functions | |||
Linear Mode
