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

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

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

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

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

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

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

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