Formula

C

canna

I have a list with the dates in column A. On sheet 2 I wan't to count the
number of entries between 2 dates.

This is what I have tried so far:

=COUNTIF(A:A,"<01/01/2005,>31/01/2005")

I want it to count how many entries are in March.

Any Ideas,

Thanks
 
B

Bob Phillips

=SUMPRODUCT(--(A1:A1000>=--"2005/03/01"),--(A1:A1000<=--"2005/03/31"))

will count between two dates, but if you just want March you can use

=SUMPRODUCT(--(MONTH(A1:A1000)=3))

0r

=SUMPRODUCT(--(TEXT(A1:A1000,"mmm")="Mar"))

Note, SUMPRODUCT does not work on a whole column, but a defined range.
 
C

canna

Thanks bj

bj said:
try
=countif(A:A,"<="&datevalue(3/31/2005))-countif(A:A,">="&datevalue(3/1/2005))
or you could set cells = meginning and end dates (B1,B2)
=countif(A:A,"<="&B1)-countif(A:A,">="&B2)
Click to expand...
 
B

bj

try
=countif(A:A,"<="&datevalue(3/31/2005))-countif(A:A,">="&datevalue(3/1/2005))
or you could set cells = meginning and end dates (B1,B2)
=countif(A:A,"<="&B1)-countif(A:A,">="&B2)
 
C

canna

Thanks Bob, your a star.

Bob Phillips said:
=SUMPRODUCT(--(A1:A1000>=--"2005/03/01"),--(A1:A1000<=--"2005/03/31"))

will count between two dates, but if you just want March you can use

=SUMPRODUCT(--(MONTH(A1:A1000)=3))

0r

=SUMPRODUCT(--(TEXT(A1:A1000,"mmm")="Mar"))

Note, SUMPRODUCT does not work on a whole column, but a defined range.
Click to expand...
 
Top