countif statements with dates

[sjmcd13]

Using: Office Excel 2003

Hello -

My overall goal is to be able to count the number of referrals received from several agencies over the course of the last year month by month.

I've used DATEVALUE to obtain the Microsoft number assigned to each date and then used that number (e.g. - 38668) in COUNTIF statements to count items after or before a particular date, such as:

=COUNTIF(C2:C23, "<38668")

However, to meet my current goal of counting referrals during, say, the month of August, but no other time, I'll need to count referrals between dates. I think this would mean using countif statements like so:

COUNTIF(C2:C23, "<38668" and ">37486")

Obviously the above formula won't work. I'm just not sure how to write the formula to count data between dates instead of just before or after a certain date.

I would greatly appreciate any help with this.

Thanks so much
sjm

 

[Aladin Akyurek]

=SUMPRODUCT((C2:C23-DAY(C2:C23)+1="1-Aug-06"+0)+0)

 

[barry houdini]

Another way...

=SUMPRODUCT(--(TEXT(C2:C23,"mmm yy")="Aug 06"))