Formula

[Guest]

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

 

[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.

 

[Guest]

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

 

[Guest]

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)

 

[Guest]

Thanks Bob, your a star.

=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.