If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
i dont know if sumif ,,,,, please help
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 |
#2
|
|||
|
|||
i dont know if sumif ,,,,, please help
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/assistan...lconPT101.aspx best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Jessejames" wrote in message ... 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 |
#3
|
|||
|
|||
i dont know if sumif ,,,,, please help
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 |
Thread Tools | |
Display Modes | |
|
|