function to count project overlap
 

i have the below function which looks for the start and end date of a
project, if it occurs within the dates provided, count it as 1.
although, if 2 projects with the same exist and overlap, eg 2 projects
called 'project1' exist in january 2006, it needs to see this and show
2. for each project it needs to increment of 1 if the project names are
the same.


Code:
--------------------
=IF(ISNA(MATCH($A4,'project list'!$A$3:$A$6,0)),0,SUMPRODUCT(--('project list'!$A$3:$A$6=Sheet1!$A4),--(C$2='project list'!$B$3:$B$6),--(C$2<='project list'!$C$3:$C$6)))
--------------------


expected results a
project list |31/12/04 |28/01/05 |25/02/05 |01/04/05
project1 |1 |1 |1 |1
project2 |0 |0 |2 |2
project3 |1 |1 |0 |0
project4 |0 |0 |0 |0

project 2 is under 25/2/05 because both projects are in progress before
1/4/05, so as they are in progress before this date, they need to be
counted. i know one of the project2's begins after 27.3.05, but it
still exists between 25/2//05 and 1/4/05.

can anyone help? im stuck, thanks

Jay


--
jay d
------------------------------------------------------------------------
jay d's Profile: http://www.excelforum.com/member.php...o&userid=34487
View this thread: http://www.excelforum.com/showthread...hreadid=568296