Keith,

Slightly different approach.

If the date in L is a number and <= b3
If the date in M is a number, M, if date in N is a number, N, else TODAY
If that date =b3 it gets counted

=SUM((Sheet1!L$7:L$100)*(Sheet1!L$7:L$10<=Sheet2! B3)*
(IF(ISNUMBER(Sheet1!M$7:M$10),Sheet1!M$7:M$10,IF(I SNUMBER(Sheet1!N$7:N$10),Sheet1!N$7:N$10,TODAY())) =Sheet2!B3))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Keith" wrote in message
...
In Sheet1, I have records in each row. The following information is part
of each record
Column L (L7:L1000) = start date for each record
Column M (M7:M1000) = initially planned end date for each record
Column N (N7:N1000) = revised end date for each record
[these ranges may contain blank or non-numeric values like "tbd"]

Now in Sheet2, I'm trying to build a list of values to sum up how many
projects were actually active during each week- so for a target date "X",
how many records had a start date before X and the end date or revised end
date after X.
I'm pulling this data from someone else's workbook, so I don't have much
control over how clean the source data is, and I'm trying to build a
formula to get the data we need and I'm close, but don't quite have it.
Any help would be appreciated.

I've started by placing in Sheet2,Column B the comparison dates (1/106,
1/8/06, 1/15/06, etc.)

I'm trying to use an Array formula in Sheet2 (column C) that references
the date in column B to get all records that were active during that span.

The following is intended to determine:
(1) is there a start date (startdate value 0) to make sure it isn't a
blank row
(2) is the start date prior to the date in this same row in Sheet2 column
B (the week I'm trying to pull data for)
(3) is the end date /or/ revised end date after the date in this same row
in Sheet2 column B (showing that the project ended or will end after the
week I'm pulling data for)

{=SUM(IF((Sheet1!L$7:L$10000<=Sheet2!B3)*(Sheet1!L $7:L$100000)*OR(Sheet1!M$7:M$10000Sheet2!B3,Shee t1!N$7:N$10000Sheet2!B3),1,0))}
translated to english: if the startdate <comparison date, and startdate
0, and either the enddate or revised enddate is greater than the
comparison date, count the record.

There is something wrong with the latter half of this formula, because I
get a running total by week that increments as records are started, but
the running list of values doesn't decrement as records pass their end
date.

I also tried a variation of the above, with an enddate checked using
(MAX(Sheet1!M$7:M$10000,Sheet1!N$7:N$10000)Sheet2 !B3) but that didn't
seem to work either.

Any suggestions would be greatly appreciated!
Thanks,
Keith