countif number of occurences per month per year.
 

I am trying to calculate the count of items between months and years.

1/1/04
12/16/04
1/1/05
12/31/04
11/16/02

I need to say the following on another sheet
Month Count
November 02 1
January 04 1
December 04 2
January 05 1

Is there a way to do this? Thank you,PETE

One way, assume the range of dates are in A2:A50, for November 2002 it would be

=SUMPRODUCT(--(YEAR(A2:A50)=2002),--(MONTH(A2:A50)=11))

or preferably

=SUMPRODUCT(--(YEAR(A2:A50)=C1),--(MONTH(A2:A50)=D1))

where you would put 2002 in C1 and 11 in D1, that way you can just change
the year and month in those cells without having to edit the formula itself




Regards,

Peo Sjoblom




"Pete Petersen" wrote:

I am trying to calculate the count of items between months and years.

1/1/04
12/16/04
1/1/05
12/31/04
11/16/02

I need to say the following on another sheet
Month Count
November 02 1
January 04 1
December 04 2
January 05 1

Is there a way to do this? Thank you,PETE

You rock!....thank you so much for the quick response...you are amazing

"Peo Sjoblom" wrote:

One way, assume the range of dates are in A2:A50, for November 2002 it would be

=SUMPRODUCT(--(YEAR(A2:A50)=2002),--(MONTH(A2:A50)=11))

or preferably

=SUMPRODUCT(--(YEAR(A2:A50)=C1),--(MONTH(A2:A50)=D1))

where you would put 2002 in C1 and 11 in D1, that way you can just change
the year and month in those cells without having to edit the formula itself




Regards,

Peo Sjoblom




"Pete Petersen" wrote:

I am trying to calculate the count of items between months and years.

1/1/04
12/16/04
1/1/05
12/31/04
11/16/02

I need to say the following on another sheet
Month Count
November 02 1
January 04 1
December 04 2
January 05 1

Is there a way to do this? Thank you,PETE