COUNTIF and dates by month/year only (not days)

[akparasite]

Hello all,

This seems trivial, but I can't figure out the best way to get my needed result.

I need to count the number of instances a date occurs within a range, but the criteria to get the result must only be defined by month/year.

I was planning on using COUNTIF to get my result, but am unsure how to format the criteria portion of the formula to return all occurrences within that month/year.

Here is a sample workbook. I want to find all dates occurring within January-02, so the COUNTIF result should be 5 in this case:

Book1
	A	B	C	D
1	SHIPDATE
2	1/14/2002		January-02
3	1/16/2002
4	1/18/2002
5	1/20/2002
6	1/22/2002
7	2/14/2002
8	2/14/2002
9	2/14/2002
10	2/14/2002
11	2/14/2002
12	2/14/2002
Sheet1

Your help is appreciated as always!

 

[eliW]

Hi,
Put 1/1/2002 in D1 for example
and 1/31/2002 in D2

Your formula will be then:

=SUMPRODUCT((A2:A12>=D1)*(A2:A12<=D2))

Eli

 

[akparasite]

That looks great. Do you think there's a way I could make it reference only one cell (i.e. Cell C1) without having to input beginning and end dates for each month?

I was playing with this formula since I submitted my initial post...but it doesn't work...

=SUMPRODUCT((TEXT($A$2:$A$12,"YYYY")=TEXT($C$2,"YYYY")*(TEXT($A$2:$A$12,"MMM")=TEXT($C$2,"MMM"))))

...obviously a problem with the formatting I'm calling upon. Any suggestions?
This message was edited by akparasite on 2002-08-22 23:19

 

[eliW]

On 2002-08-22 23:19, akparasite wrote:
That looks great. Do you think there's a way I could make it reference only one cell (i.e. Cell C1) without having to input beginning and end dates for each month?

I was playing with this formula since I submitted my initial post...but it doesn't work...

=SUMPRODUCT((TEXT($A$2:$A$12,"YYYY")=TEXT($C$2,"YYYY")*(TEXT($A$2:$A$12,"MMM")=TEXT($C$2,"MMM"))))

...obviously a problem with the formatting I'm calling upon. Any suggestions?
This message was edited by akparasite on 2002-08-22 23:19

=SUMPRODUCT((TEXT(A2:A12,"mmm/yyyy")=TEXT(C2,"mmm/yyyy"))*1)

Eli

 

[akparasite]

Thank you Eli!!!!