Sum question

K

Kai

In one sheet, there are two columns, one is named product item and another is
quantity. Since there are many product item number, some of them are same and
some of them are different. I want to sum all the item quantity which have
the same product item number and show it in a new column.
What should I do?

Thanks a lot.
 
M

Max

One way is to use SUMIF

Assume you have in Sheet1, cols A and B
data from row2 down ..

Prod Qty
111 100
112 200
111 150
112 250
etc

In Sheet2
-------------
With the product #s listed in A2 down

Prod Qty
111 ?
112 ?
113 ?

Put in B2: =SUMIF(Sheet1!A:A,A2,Sheet1!B:B)
Copy down

Col B will return the total Qty
for each product #s listed from Sheet1, viz.
for the sample data in Sheet1, it'll show as:

Prod Qty
111 250
112 450
113 0
 
K

Kai

Thank you very much Max,

But another problem is how can I show only one product item number in Sheet2
since there are many product item number.

Thanks.
Kai

"Max" 來函：
 
M

Max

Try this to extract the list of uniques ..

In Sheet1
-------------
Select col A
(the "Prod" column)

Prod Qty
111 100
112 200
111 150
112 250
etc

Click Data > Filter > Advanced Filter
Check "Copy to another location"
Ensure the "List range" box reads as: A:A
Put in "Copy to:" box : D1
Check "Unique records only"
Click OK

The list of unique product #s will be extracted
into col D, viz.:

Prod
111
112

Now just cut out col D and paste into col A in Sheet2
 
Top