Counting with Date range

[Matt]

I've got a customer that needs a formula that counts the
number of events that take place within a specific date
range. This is an example of the data below:
A B
Date Event #
2/12/04 256
2/18/04 260
3/15/04 265
4/18/04 300

We need a formula that will count the event that falls
with a given month, so that something like the following
results are produced:
February 2
March 1
April 1

Thanks!
Matt

 

[Arvi Laanemets]

Hi

For February
=SUMPRODUCT(($A$2:$A$1000>DATE(2004,2,0)*($A$2:$A$1000<DATE(2004,3,1))

 

[Dave R.]

Something perhaps like this;

=SUMPRODUCT(--(MONTH(A$1:A$4)=3))

the =3 part is what tells it what month it's looking for (1=jan, 2=feb,
etc.)

 

[Frank Kabel]

Hi Matt
1. for a formula approach use something like (for January 2004)
=SUMPRODUCT(--(YEAR($A$2:$A$1000)=2004),--(MONTH($A$2:$A$1000)=1))

2. you may take a look at pivot tables for this:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm