counting cells containing date values

[langdon37]

Ok so I have a sheet whereby column A holds dates throughout the year.

What I need to do is, on sheet 2 is to have a formula which will count all cells in column A containing dates from 1/1/2009 to 31/1/2009, Another column will count all dates from 1/2/2009 to 28/2/2009 and so on. I have tried the countif format but this does not seem to work. If you should have any ideas great.

many thanks.

[oldchippy]

Hi,

You could put a start date in one cell and the end date in another and use something like

=SUMPRODUCT(--(A1:A24<=C2),--(A1:A24>=C1)) where C1 hold start and C2 hold end date

or

=SUMPRODUCT(--($A$1:$A$24>=C1),--($A$1:$A$24<=EOMONTH(C1,0))) where C1 holds start date and use EOMONTH for the end date of the month

[shg]

=COUNTIF(A1:A10, ">=1/1/09") - COUNTIF(A1:A10, ">1/31/09"), adjusted for the funny way you guys write dates ...

[duanzhuanming]

For Excel 2007:
=COUNTIFS(A1:A24,">=1/5/2009",A1:A24,"<1/7/2009")

[langdon37]

Thanks shg, worked a treat.