Formula to determine number of current records by week

[Keith]

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$10000>0)*OR(Sheet1!M$7:M$10000>Sheet2!B3,Sheet1!N$7:N$10000>Sheet2!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

 

[TerryD]

If the excel sheet properties and columns are not changing and you have and
know how to use access I believe it would be easier to just link that sheet
to a access database and have a couple of queries to pull thte data that you
are looking for.

 

[Bob Phillips]

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$10>0)*(Sheet1!L$7:L$10<=Sheet2!B3)*
(IF(ISNUMBER(Sheet1!M$7:M$10),Sheet1!M$7:M$10,IF(ISNUMBER(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)

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)

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

 

[Lori]

The trouble is function like OR and MAx return single value when used
with arrays.
Try instead:

=SUMPRODUCT((Sheet1!L$7:L$10000<=Sheet2!B3)*(Sheet1!L$7:L$10000>0)*
((Sheet1!M$7:M$10000>Sheet2!B3+Sheet1!N$7:N$10000>Sheet2!B3)>0))

Note that sumproduct formulas don't need to be array-entered.

 

[Keith]

Brilliant! Thanks Bob!
Keith

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$10>0)*(Sheet1!L$7:L$10<=Sheet2!B3)*
(IF(ISNUMBER(Sheet1!M$7:M$10),Sheet1!M$7:M$10,IF(ISNUMBER(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)

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)

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

 

[Keith]

Ah, that's a good learning for me. Is there a list of which functions (in
addition to OR and MAX) are subject to this restriction?
Thanks Lori,
Keith

The trouble is function like OR and MAx return single value when used
with arrays.
Try instead:

=SUMPRODUCT((Sheet1!L$7:L$10000<=Sheet2!B3)*(Sheet1!L$7:L$10000>0)*
((Sheet1!M$7:M$10000>Sheet2!B3+Sheet1!N$7:N$10000>Sheet2!B3)>0))

Note that sumproduct formulas don't need to be array-entered.

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)

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

 

[Lori]

Hi Keith - I know the last post wasn't very clear.

The point is that all summary and logical functions which allow arrays
as arguments such as sum, average, product, and, etc. will only ever
return a single value.

However there are a few functions that can return arrays when used
with the offset and indirect functions, these include n, t, subtotal,
sumif, countif and database-functions.

Ah, that's a good learning for me. Is there a list of which functions (in
addition to OR and MAX) are subject to this restriction?
Thanks Lori,
Keith

The trouble is function like OR and MAx return single value when used
with arrays.
Try instead:

=SUMPRODUCT((Sheet1!L$7:L$10000<=Sheet2!B3)*(Sheet1!L$7:L$10000>0)*
((Sheet1!M$7:M$10000>Sheet2!B3+Sheet1!N$7:N$10000>Sheet2!B3)>0))

Note that sumproduct formulas don't need to be array-entered.

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$10000>0)*OR(Sheet1!M$7:M$10000>Sheet2!B3,Sheet1!N$7:N$10000>Sheet2!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