From: Jessejames on 19 Apr 2010 08:54 i have 2000 rows and 2000 columns in one column, after searching for this product x. this value of all the product x will be summed up on below , for every search, there will be automatic sum for every product.(is it possible?) this is sample data (these are filtered items) col b col n row 5 product x 1000 6 product x 1500 row 2001 product x ( ??? ) at row 2001 (this will be the formula bar that will calculate the col n of the said product. if i search product y, the same formula which product x defined. it will input the product y and the total ( ??? ) and so on.... IS IT CLEAR? -- xxxJessexxx From: Bernard Liengme on 19 Apr 2010 09:09 Suppose the original data is item number a 2 a 4 b 6 b 3 a 5 c 4 a 5 c 7 Make a copy of the first column of the data on a new sheet On the new sheet use Data | Filter | Advanced to get a list of the unique items Use a formula like =SUMIF(Sheet1!A:A,A2,Sheet1!B:B) in B2 of the new sheet to total all the B (and other) columns item total a 16 b 9 c 11 Alternatively, lean about Pivot Tables Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Jessejames" wrote in message news:62980142-FB52-4DF6-8BC0-E2FC74EA7D0A(a)microsoft.com...> i have 2000 rows and 2000 columns > in one column, after searching for this product x. this value of all the > product x will be summed up on below , for every search, there will be > automatic sum for every product.(is it possible?) > this is sample data > > (these are filtered items) > col b col n > row 5 product x 1000 > 6 product x 1500 > > row 2001 product x ( ??? ) > > at row 2001 (this will be the formula bar that will calculate the col n of > the said product. > if i search product y, the same formula which product x defined. it will > input the product y and the total ( ??? ) > and so on.... > > IS IT CLEAR? > > > > > > > > > > > -- > xxxJessexxx From: Eduardo on 19 Apr 2010 09:14 Hi, Not sure if I understood you, you want to filter let' say product X and obtain the sum in column N only for that product , in column N row 2001 enter this formula =subtotal(9,N1:N200) it will change the total to the filtered rows "Jessejames" wrote: > i have 2000 rows and 2000 columns > in one column, after searching for this product x. this value of all the > product x will be summed up on below , for every search, there will be > automatic sum for every product.(is it possible?) > this is sample data > > (these are filtered items) > col b col n > row 5 product x 1000 > 6 product x 1500 > > row 2001 product x ( ??? ) > > at row 2001 (this will be the formula bar that will calculate the col n of > the said product. > if i search product y, the same formula which product x defined. it will > input the product y and the total ( ??? ) > and so on.... > > IS IT CLEAR? > > > > > > > > > > > -- > xxxJessexxx  |  Pages: 1 Prev: Date refrencingNext: Drop Downs and lookups from other sheets