Combining same data in multiple rows

[Susienak]

I have two columns of information: Column A has a number that represents a
product and Column B shows how many times that product was sold. I combined
the data from multiple worksheets so the product numbers repeat:

Prod# Product sales
610 3
611 1
612 5
612 1
612 3
613 5
613 1
613 2
614 1
614 4
614 1
622 1
622 3
622 2
623 2
623 12
623 2
624 2

How do I combine the product numbers in Column A to give me the total
products sold in Column B. Ex: Product#623 was sold twice today from one
website, 12 times from another website, and twice from yet another website
yielding 16 sales for product #623.

My list contains 30,000 numbers and Im looking for it not to sum up the
product numbers, just yield one sum for each product number. Help please!!!

 

[Stefi]

Create a unique list of products in, say, column C:
Data>Advanced filter>Check Copy to another place checkbox, List range:
$A:$A, Filter range: $A:$A, Copy to: $D$1

Enter this formula in E2 and fill it down:

=SUMIF(A:A,C2,B:B)

Regards,
Stefi

„Susienak†ezt írta:

 

[Max]

A pivot table can get you there in a matter of seconds

Assuming the table posted is in cols A & B,
headers in row1, data from row2 down

Steps (in xl2003)
Select any cell within the data,
click Data > PivotTable...
Click Next>Next
In step 3 of the wiz, click Layout
Drag n drop "Prod#" within ROW area
Drag n drop "Product sales" within DATA area
(It'll appear as Sum of Product sales)
Click OK > Finish. That's it!

Hop over to the pivot in the new sheet to the left,
where it'll show all the "Prod#"'s in the left col,
with corresponding Sum of Product sales next to it.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,700 Files:356 Subscribers:53
xdemechanik

 

[Susienak]

Hi Max,

I tried Pivot table the other day but I was missing something that I didnt
figure out until today. 2007 works a little different. I highighted the two
columns, went to the insert tab, clicked pivot table and ok, then dragged the
prod# to the ROWS field on the bottom right and dragged the product sales to
the VALUES field on the bottom right... BUT that automatically gives a
"count".. it counts how many times the Product number appears in the product
# row, it doesnt sum up the products sold... SO... on the bottom right under
VALUES you click on the drop down arrow and you can change the field settings
from a count to a sum... and that worked perfectly!

Thanks...

 

[Max]

Glad it helped you to find the solution in your xl2007 <g>

Thanks for posting the steps in xl2007,
which benefits many other readers facing the same issue

Btw, do take a moment to press the Yes buttons (like the one below) from
where you're reading this, for all responses which helped answer your query.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,700 Files:356 Subscribers:53
xdemechanik