| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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")) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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")) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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")) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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")) . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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")) . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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")) . . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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")) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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")) . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
| Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
| Sumproduct how can i use | Excel Discussion (Misc queries) | |||
| sumproduct help. | Excel Worksheet Functions | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Linear Mode
