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