Add similar name amounts

[georgei]

Hi all,

I have a spreadsheet with 2 workbooks, one the first book I have a column with a long list of names and 2nd column with different amounts next to each name. A name will repeat several times and I need to add all the amounts belonging to each name and show in a table in another workbook.

here's an example:

Name               Amount
------------------------
Jonathan          45
George             60
Jane                -35
Jonathan          15
David               -40
Jonathan          -20
Jane                35
George             50
David               -30

So basically what I'm looking for is to create a separate table on another workbook and have Excel list each name in that column only once, and in the next cell show a "total" amount for each person. So for example Jonathan would be listed with 40, George with 110

Thanks in advance if you can help!

[DonkeyOte]

Use a Pivot Table - set Name as Row Field (label pending version) and set Amount as Data Field.

General intro. link to Pivots in my sig.

[dominicb]

Good afternoon georgei

So basically what I'm looking for is to create a separate table on another workbook and have Excel list each name in that column only once, and in the next cell show a "total" amount for each person. So for example Jonathan would be listed with 40, George with 110

What you need to use is a pivot table (Data > Pivot table and pivot chart report). I can't really explain how they work here, but I suggest you don't look at the online help but just dive in and see how you go.

If you really need some instruction you might like to have a look at Jon Peltier's introduction to pivot tables here.

HTH

DominicB

[georgei]

I tried creating a Pivot Table, but couldn't figure it out.
I tried selecting a range that include the column headings, and without the headings row, but I still get this error...
"The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns."

My spreadsheet goes from A to V columns, name is in A and amount column is V, the last one. There are some empty columns in between, but I need them there.

Is there anything else I can try?