Help with SumProduct
 

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"))

Help with SumProduct
 

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" wrote in message
...
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"))

Help with SumProduct
 

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"))

Help with SumProduct
 

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" wrote in message
...
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"))


.

Help with SumProduct
 

T5 contains a date

Ok, but that didn't answer my questions.

So, try this...

Create these named ranges

InsertNameDefine
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" wrote in message
...
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" wrote in message
...
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"))


.

Help with SumProduct
 

What doesn't the 1E100 do in the formula?

"T. Valko" wrote:

T5 contains a date

Ok, but that didn't answer my questions.

So, try this...

Create these named ranges

InsertNameDefine
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" wrote in message
...
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" wrote in message
...
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"))


.



.

Help with SumProduct
 

Hi,

Select I4:K2000 (or the entire range including more columns to the
left/right) and convert it to a List/Table (Ctrl+L) - this feature is
available Excel 2003 onwards. I have assumed row 4 has headers.

When you convert a range to a List, it becomes auto expanding. The caveat
here is that data should be entered in consecutive rows I.e. no row should
be left blank

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Ayo" wrote in message
...
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"))

Help with SumProduct
 

1E100 is scientific notation for a very large number. It's used to find the
last numeric value in the range.

--
Biff
Microsoft Excel MVP


"Ayo" wrote in message
...
What doesn't the 1E100 do in the formula?

"T. Valko" wrote:

T5 contains a date

Ok, but that didn't answer my questions.

So, try this...

Create these named ranges

InsertNameDefine
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" wrote in message
...
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" wrote in message
...
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"))


.



.