Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 0 down vote favorite
1

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?

share|improve this question

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

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2 Answers

up vote 6 down vote

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:

enter image description here

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

enter image description here

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.

enter image description here

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!

share|improve this answer
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
up vote 0 down vote

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.

share|improve this answer
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
1  
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

Not the answer you're looking for? Browse other questions tagged or ask your own question.