Counting Dates #2

[Guest]

What formula to read a range of cells with a lot of dates in it and than
count how many times the current month shows up in it (has to recognize the
year too because if it only counts all dates with November in them it cant
count 2003, 2004 etc.. only the current month)

Example:

11/4/2005
11/9/2003
11/12/2005
12/1/2005

This would need to give me a total of: 2

Thank you,

 

[Biff]

Hi!

Try one of these:

=SUMPRODUCT(--(TEXT(A1:A4,"mmmyyyy")="Nov2005"))

OR:

B1 = 'Nov2005

=SUMPRODUCT(--(TEXT(A1:A4,"mmmyyyy")=B1))

OR:

=SUMPRODUCT(--(MONTH(A1:A4)=MONTH(TODAY())),--(YEAR(A1:A4)=YEAR(TODAY())))

Biff

 

[Bob Phillips]

=SUMPRODUCT(--(DATE(YEAR(A2:A20),MONTH(A2:A20),1)=--"2005-11-01"))


the thing here is always to test against the first of the month in question

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

One more...

=COUNTIF(A1:A10,"<="&DATE(2005,11,30)) - COUNTIF(A1:A10,"<"&DATE(2005,11,1))

======
If you have lots of these to calculate, you may want to try a pivottable. Then
group by month and year.