Need a big hand with this one

  • Thread starter Thread starter PCOR
  • Start date Start date
P

PCOR

I have a work sheet that looks something this
Col A Col B
1 10
1 20
2 5
4 1
4 5
4 6
10 4
10 8
100 12

The result I would like to obtain is:
1 30
2 5
4 12
10 12
100 12

In other words , I want to add up the numbers in Col B where the number in
col A is the same(ie col A has two 1 .THe equivalent numbers in Col B add up
to 30)
TIA
 
Apply Advanced Filter to obtain a unique list of numbers from column into
column, say, E.

In F2 enter & copy down:

=SUMIF($A$2:$A$10,E2,$B$2:$B$10)
 
PCOR,

This is very easy to do using the SUMIF formula.

essentially if you wanted your second list in D1:E5
in D1 put 1 in E1 put =SUMIF(A:A,D1,B:B)
in D2:D5 put 2,4,10,100 and drag the formula in E1
down through E5.

I've assumed you have nothing else in columns A&B
if you do use

=SUMIF(A$1:A$9,D1,B$1:B$9) and drag down.

Dan E
 
The quickest solution is to use a Pivot Table.

Choose "Data/Pivot Table..." and put your Column A data in
the "Row" area of the layout and your Column B data in
the "Data" area of the layout.

This will automatically group your Column A unique values
and combine the Column B data into a sum for each Column A.

HTH,

Allison
 
I guess what I failed to tell you is that I have abput 13000 numbers in Col
A
How do I proceed now(pls give much detail)
Thansk
 
I am afraid that I did not have much luck.
I am using Excel 2003, can you please give as much detail as possible
inorder to solve this
Thanks
 
Hi
Many Thanks...I got it going using your method
Hope to hear from the others as I am willing to learn all I can
Thanks again
 
As Allison said - Pivot Pivot Pivot Pivot Pivot!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Your data is made for a Pivot table, and you honestly have no idea how much you will appreciate
one until you understand what it is able to do for you. This is one of the most powerful, yet
simple features that Excel actually has IMO. Take a look at Debra Dalgleish's site for a good
intro:-

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm

If you still have problems then please post back, and we'll help you sort it. If push comes to
shove and you still have problems then by all means mail me your data if possible and I will throw
it into some example tables to show you what it can do. This is *so* worth taking some time to
understand, I really can't stress it enough. people in the Office think it is nothing short of
magic when they see what a Pivot Table can do for this kind of data, and the best part is that it
is so simple to do.
 
The other option (But don't let that stop you learning Pivot Tables), is to use Data / Subtotals,
and then say for every change in Col A put a SUM in Col B. This lets you click on a single button
to get you what you want wrt the example you gave.
 
Back
Top