Frequency of date

P

PAL

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.
 
J

Jacob Skaria

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
 
P

PAL

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.
 
T

T. Valko

=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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top