# How to sum a set of data in Excel? [duplicate]

Possible Duplicate:
Excel Sum that excludes duplicates

I need to sum a set of data in Excel without any duplicates - the best way to explain is with the following:

As a simple example I can give this table:

``````a   10
b   20
c   30
a   1
a   2
b   2
``````

As a result I want to get:

``````a   13
b   22
c   30
``````

Does anyone know how to do this?

-

## marked as duplicate by studiohack♦Dec 31 '11 at 23:59

You can create a new column to get every a,b,c without any duplicates. This is done with an `array formula` as explained here:

http://www.cpearson.com/excel/ListFunctions.aspx

You then only have to use the `SUMIF()` function to get the sum.

-
This is also a great idea, Max. The only problem in my case is that the data will be extracted dynamically and I won't know the actual values to sum on. Thanks again! –  user88096 Jun 29 '11 at 13:13
Actually, Max's idea suggests simplicity itself. Assuming all your letters are in column A, and your data is in column B, then, off to the side you'd have =SUMIF(A:A,"a",B:B) for your "a" values, =SUMIF(A:A,"b",B:B) for your "b" values, =SUMIF(A:A,"c",B:B) for your "c" values. That way, it shouldn't matter how many values you have. –  F106dart Jun 30 '11 at 19:45

It's a breeze with a pivot table! And, this is a great chance to learn this super-powerful feature of Excel with your simple example data.

You already have your data in 2 columns. You should then put names at the top of your 2 columns, such as:

Next (in Excel 2007 or higher), choose "Insert", "Pivot Table". This brings up the "Create PivotTable" dialog box.

In the "Create Pivot Table" dialog box, in the top section, select your 2 columns of data to include the column names. In the lower section, you can specify the location of your new pivot table. If you choose an "Existing Worksheet", just be sure that you aren't overlapping any cells that are already occupied. Click "OK".

This brings up a "Pivot Table Field List" form.

Using the column names shown above, you would drag the "Ltr" from the "Choose fields..." section into the "Row Labels" section. You would drag the "Val" into the "Sum Values" section. All done!

One final trick. If you only need the layout of the resulting pivot table, but don't need all the functionality (filtering, drag-and-drop, etc), click anywhere in the pivot table. Then on the Ribbon, choose "Options", "Select", "Entire Pivot Table". Then, "copy" and then in a desired location, "Paste Special", and choose "Values". You'll get the layout only at that point.

Hope this helps!

-
many thanks for the great lesson!!! This will certainly do the job! –  user88096 Jun 29 '11 at 12:59
the Pivot Table of course ! my bad i didn't think about it before ! great for user88096 you found a good solution (btw, don'f forget to accept F106dart's answer if that works as expected) –  JMax Jun 29 '11 at 13:30
Glad y'all like it. Pivot tables are handier than a shirt pocket! –  F106dart Jun 29 '11 at 14:08