Help with SumProduct
in [Functions]
|
From: Ayo on 16 Mar 2010 16:05 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 16 Mar 2010 16:36 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 16 Mar 2010 17:09 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 16 Mar 2010 21:56 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 16 Mar 2010 22:40 >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")) >> >> >> . >>
|
Next
|
Last
Pages: 1 2 Prev: Excel 2007 - worksheets macro Next: If and LOOKUP - results in multiple columns |