Help with SumProduct
in [Functions]
|
From: Ayo on 17 Mar 2010 13:53 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 > > 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")) > >> > >> > >> . > >> > > > . >
From: Ashish Mathur on 17 Mar 2010 19:15 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" <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 17 Mar 2010 20:21 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" <Ayo(a)discussions.microsoft.com> wrote in message news:351215B2-B1EB-495D-A9FA-0DBF6AD49A50(a)microsoft.com... > 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 >> >> 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")) >> >> >> >> >> >> . >> >> >> >> >> . >>
First
|
Prev
|
Pages: 1 2 Prev: Excel 2007 - worksheets macro Next: If and LOOKUP - results in multiple columns |