array Formula for date counting

  • Thread starter Thread starter Bill Dunn
  • Start date Start date
B

Bill Dunn

I have three columns:
A: Project Name
B: Project Start Date (i.e. 2/1/2004)
C: Project End Date (i.e. 4/31/2004)

There are about 200 projects planned for the 2004 year.

I'd like to know how many projects are active in each month. I'll have each
month's name in columns E-P, and the number of active projects below. I
suspect it's an array, but I can't figure it out when using "<=" and ">="
for each month. Any help is appreciated .

Thanks
Bill
 
Hi
if you have the starting date and the ending date on a separate sheet
in column A and B (e.g. 1/1/2004 and 2/1/2004) try the following
formula in C1
=SUMPRODUCT(--('sheet1'!$C$1:$C$200>=A1),--('sheet1'!$B$1:$B$200<=B1))
 
Thanks Frank,
I found my problem. I am using the Sumproduct function, but needed to
include the DATEVALUE function in my statement when comparing dates.

Bill
 
Back
Top