Help with Dynamic range in formula

[hammers1]

Hello
I have defined a dynamic range called opening date
=OFFSET(Support!$B$1,0,0,COUNTA(Support!$B:$B),1)

I have also defined a static named range called testopening_date
=Support!$B1:B1501

I am then using the following function which works
=SUM(IF((Support!$B$1:$B$1501<=G87)*(Support!$B$1:$P$1501="")+IF(Support!$P$1:$P$1501>G87,Support!$B$1:$B$1501<=G87),1,0))

with a named range which works
=SUM(IF((testopening_date<=G87)*(Support!$P$1:$P$1501="")+IF(Support!$P$1:$P$1501>G87,testopening_date<=G87),1,0)) # This works

but if I use the dynamic range I get a #N/A error
=SUM(IF((opening_date<=G87)*(Support!$P$1:$P$1500="")+IF(Support!$P$1:$P$1500>G87,opening_date<=G87),1,0))

Column B contains date an issue was opened
Column P contains date that issue was closed , if the issue has not been closed then the cell in Column C will be blank. Every issue will always have an opened date in column B

Cell G87 contains the a date which I use as the end of the month date and the idea is that I can get a count of how many issues were still open at the end of each month and including the current open issue at any time. This will be the end date of the current month or the end date of the previous month.
I also have a Column S that has “YES” for open and “NO”for closed, this column will always be populated with YES or No depending on ticket status

The above works fine for previous months, but it becomes a problem when I try to get the current months open issues.
1) As the range is growing if I use a static range ( B1:B1500 or a named range ) I have to keep updating the range as it grows
2) I can’t define a larger range as I would then count the blank cells in Column P and this make the current open tickets value wrong
3) How can I used a dynamic range for column B
4) And how can I set up a dynamic date range for Column P as that could contain a number of blank cells

This what I am trying to get

2005 Jan Feb March April
Opened 81 93 72 25
Fixed 68 119 71 22
Run Total 85 59 60 64

ST Month 01/01/2005 31/01/2005 28/02/2005 01/04/2005
Ed Month 30/01/2005 27/02/2005 01/04/2005 29/04/2005

Sample data
Col B Col P Col S
Opened Closed Active
01/02/2005 YES
12/02/2005 14/02/2005 NO
12/02/2005 YES
01/04/2005 01/04/2005 NO

 

[SkipVought]

Hi,

the key to dynamic ranges is to COUNTA on ANY COLUMN/ROW that has no empty cells in the data range.

Here's a solution with the SUMPRODUCT function. I also gave the Closed_Date range a DYNAMIC name as well...
[tt]
Opening_Date: =OFFSET(Support!$B$2,0,0,COUNTA(Support!$B:$B)-1,1)
Closed_Date: =OFFSET(Support!$P$2,0,0,COUNTA(Support!$B:$B)-1,1)

=SUMPRODUCT((Opening_Date<TargetDate)*(Closed_Date=""))+SUMPRODUCT((Closed_Date<TargetDate)*(Opening_Date<=TargetDate))
[/tt]


Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[hammers1]

Hello Skip
Thanks for the help, when i try it on my sheet i get a value of 1565 , when I am expecting a value of 62, which I get by using static ranges.
=SUM(IF((Support!$B$1:$B$1501<=G87)*(Support!$P$1:$P$1501="")+IF(Support!$P$1:$P$1501>G87,Support!$B$1:$B$1501<=G87),1,0))

=SUMPRODUCT((FXopening_date<G87)*(FXclosing_date=""))+SUMPRODUCT((FXclosing_date<G87)*(FXopening_date<=G87))

FXopening = =OFFSET(Support!$B$1,0,0,COUNTA(Support!$B:$B)-1,1)
FXclosing = =OFFSET(Support!$P$2,0,0,COUNTA(Support!$B:$B)-1,1)
G87 has a value of 29/04/2005

Would you mind explaining how each part of the function should work. Is there a way to see what the function is doing.
As I understand it this is multiplying opening date that is less than 29/04/2005 by closing date that has blank cells (i.e still open) and then adding the value of multiplying closing date that is less that 29/04/2005 by opening date that is less than 29/04/2005

Or have I got it totally wrong, once again any help you can give is much appreciated

 

[SkipVought]

First of all, the first two formulas you posted are not using the same criteria.

Here's what this ARRAY expression is doing...
[tt]
=SUMPRODUCT((FXopening_date<G87)*(FXclosing_date=""))+SUMPRODUCT((FXclosing_date<G87)*(FXopening_date<=G87))
[/tt]
lets say that FXopening_date has the followint 3 cells...
[tt]
30/04/2005
29/04/2005
28/04/2005
[/tt]
[tt]
(FXopening_date<G87)
30/04/2005: FALSE
29/04/2005: FALSE
28/04/2005: TRUE
[/tt]
which is identical to
[tt]
30/04/2005: 0
29/04/2005: 0
28/04/2005: 1
[/tt]
This happend for EACH of the expression with a criteria.


Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}