Frequency of date
 

I have a list of dates over a two year period (in this example it is over
2006-07; although it will vary given different data sets). I would like to
create a table show the frequency of dates in each month.

Jan -06 5
Feb -06 1

Is there an easy way to do this count.

Frequency of date
 

By far the easiest way is a pivot table. If you're new to Pivot Tables,
start he
http://peltiertech.com/Excel/Pivots/pivottables.htm

Regards,
Fred.

"PAL" wrote in message
...
I have a list of dates over a two year period (in this example it is over
2006-07; although it will vary given different data sets). I would like
to
create a table show the frequency of dates in each month.

Jan -06 5
Feb -06 1

Is there an easy way to do this count.

Frequency of date
 

If you mean to count the number of entries in a particular month

'count of number of entries of nov 2009 from Col A
=SUMPRODUCT(--(TEXT(A1:A100,"mmyyyy")="112009"))

Or with the current month/date in cell C1

=SUMPRODUCT(--(TEXT(A1:A100,"mmyyyy")=TEXT(C1,"mmyyyy")))

If this post helps click Yes
---------------
Jacob Skaria


"PAL" wrote:

I have a list of dates over a two year period (in this example it is over
2006-07; although it will vary given different data sets). I would like to
create a table show the frequency of dates in each month.

Jan -06 5
Feb -06 1

Is there an easy way to do this count.

Frequency of date
 

Jacob,

I modified the first part of your formula in order to make it work for
whatever is in B1. I get blanks. As an array.....

=IF('Site Raw'!$J$5:$J$1021=$B$1,SUMPRODUCT(--(TEXT('Site
Raw'!$C$5:$C$1021,"mmyyyy")=TEXT(A4,"mmyyyy"))),"" )

Any ideas.




"Jacob Skaria" wrote:

If you mean to count the number of entries in a particular month

'count of number of entries of nov 2009 from Col A
=SUMPRODUCT(--(TEXT(A1:A100,"mmyyyy")="112009"))

Or with the current month/date in cell C1

=SUMPRODUCT(--(TEXT(A1:A100,"mmyyyy")=TEXT(C1,"mmyyyy")))

If this post helps click Yes
---------------
Jacob Skaria


"PAL" wrote:

I have a list of dates over a two year period (in this example it is over
2006-07; although it will vary given different data sets). I would like to
create a table show the frequency of dates in each month.

Jan -06 5
Feb -06 1

Is there an easy way to do this count.

Frequency of date
 

=IF('Site Raw'!$J$5:$J$1021=$B$1,SUMPRODUCT(--(TEXT('Site
Raw'!$C$5:$C$1021,"mmyyyy")=TEXT(A4,"mmyyyy")))," ")

Not sure what you're trying to do but you'd have to put the IF test inside
SUMPRODUCT like this:

=SUMPRODUCT(--(TEXT('Site
Raw'!$C$5:$C$1021,"mmyyyy")=TEXT(A4,"mmyyyy")).--('Site
Raw'!$J$5:$J$1021=$B$1))


--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
Jacob,

I modified the first part of your formula in order to make it work for
whatever is in B1. I get blanks. As an array.....

=IF('Site Raw'!$J$5:$J$1021=$B$1,SUMPRODUCT(--(TEXT('Site
Raw'!$C$5:$C$1021,"mmyyyy")=TEXT(A4,"mmyyyy"))),"" )

Any ideas.




"Jacob Skaria" wrote:

If you mean to count the number of entries in a particular month

'count of number of entries of nov 2009 from Col A
=SUMPRODUCT(--(TEXT(A1:A100,"mmyyyy")="112009"))

Or with the current month/date in cell C1

=SUMPRODUCT(--(TEXT(A1:A100,"mmyyyy")=TEXT(C1,"mmyyyy")))

If this post helps click Yes
---------------
Jacob Skaria


"PAL" wrote:

I have a list of dates over a two year period (in this example it is
over
2006-07; although it will vary given different data sets). I would
like to
create a table show the frequency of dates in each month.

Jan -06 5
Feb -06 1

Is there an easy way to do this count.