Prev: Date refrencing
Next: Drop Downs and lookups from other sheets
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" <Jessejames(a)discussions.microsoft.com> 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 refrencing
Next: Drop Downs and lookups from other sheets