From: Ayo on
Is there a function in excel that would allow me to replace the $2000 in the
formular below with the last row in the column contain values.
Somthing like this, in VBA:
errorWS_lastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row

=SUMPRODUCT((Current_MarketList!$I$5:$I$2000<$T$5)*(Current_MarketList!$K$5:$K$2000="PAST DUE"))
From: T. Valko on
You can use a dynamic range.

Are there any empty cells *within* the range I5:I2000? It looks like that
range contains numbers, are there any text entries in that range?

--
Biff
Microsoft Excel MVP


"Ayo" <Ayo(a)discussions.microsoft.com> wrote in message
news:E9C12940-4BCE-4BA6-9208-69EB4E1C727A(a)microsoft.com...
> Is there a function in excel that would allow me to replace the $2000 in
> the
> formular below with the last row in the column contain values.
> Somthing like this, in VBA:
> errorWS_lastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row
>
> =SUMPRODUCT((Current_MarketList!$I$5:$I$2000<$T$5)*(Current_MarketList!$K$5:$K$2000="PAST
> DUE"))


From: Tom Hutchins on
Try

=SUMPRODUCT((Current_MarketList!$I$5:OFFSET($I$1,0,0,MATCH(1E+300,$I:$I))<$T$5)*(Current_MarketList!$K$5:OFFSET($I$1,0,2,MATCH(1E+300,$I:$I))="PAST DUE"))

Hope this helps,

Hutch

"Ayo" wrote:

> Is there a function in excel that would allow me to replace the $2000 in the
> formular below with the last row in the column contain values.
> Somthing like this, in VBA:
> errorWS_lastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row
>
> =SUMPRODUCT((Current_MarketList!$I$5:$I$2000<$T$5)*(Current_MarketList!$K$5:$K$2000="PAST DUE"))
From: Ayo on
T5 contains a date

"T. Valko" wrote:

> You can use a dynamic range.
>
> Are there any empty cells *within* the range I5:I2000? It looks like that
> range contains numbers, are there any text entries in that range?
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Ayo" <Ayo(a)discussions.microsoft.com> wrote in message
> news:E9C12940-4BCE-4BA6-9208-69EB4E1C727A(a)microsoft.com...
> > Is there a function in excel that would allow me to replace the $2000 in
> > the
> > formular below with the last row in the column contain values.
> > Somthing like this, in VBA:
> > errorWS_lastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row
> >
> > =SUMPRODUCT((Current_MarketList!$I$5:$I$2000<$T$5)*(Current_MarketList!$K$5:$K$2000="PAST
> > DUE"))
>
>
> .
>
From: T. Valko on
>T5 contains a date

Ok, but that didn't answer my questions.

So, try this...

Create these named ranges

Insert>Name>Define
Name: Dates
Refers to:

=Current_MarketList!$I$5:INDEX(Current_MarketList!$I$5:$I$2000,MATCH(1E100,Current_MarketList!$I$5:$I$2000))

Adjust for a reasonable end of range $I$2000

Name: Status
Refers to:

=Current_MarketList!$K$5:INDEX(Current_MarketList!$K$5:$K$2000,MATCH(1E100,Current_MarketList!$I$5:$I$2000))

Adjust for a reasonable end of ranges $K$2000 and $I$2000

OK out

Then:

=SUMPRODUCT(--(Dates<$T$5),--(Status="PAST DUE"))

--
Biff
Microsoft Excel MVP


"Ayo" <Ayo(a)discussions.microsoft.com> wrote in message
news:8A2370D2-0BE4-4632-9AB7-D09966EF37B4(a)microsoft.com...
> T5 contains a date
>
> "T. Valko" wrote:
>
>> You can use a dynamic range.
>>
>> Are there any empty cells *within* the range I5:I2000? It looks like that
>> range contains numbers, are there any text entries in that range?
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Ayo" <Ayo(a)discussions.microsoft.com> wrote in message
>> news:E9C12940-4BCE-4BA6-9208-69EB4E1C727A(a)microsoft.com...
>> > Is there a function in excel that would allow me to replace the $2000
>> > in
>> > the
>> > formular below with the last row in the column contain values.
>> > Somthing like this, in VBA:
>> > errorWS_lastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row
>> >
>> > =SUMPRODUCT((Current_MarketList!$I$5:$I$2000<$T$5)*(Current_MarketList!$K$5:$K$2000="PAST
>> > DUE"))
>>
>>
>> .
>>