Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Here is my data in sheet1 of excel:


A1 20

A2 2

A1 2

A3 3

Consider the above in an excel sheet. Column A contains duplicate keys and Column B contains quantity. my requirement is, I need a function in Excel which can add values of B for unique values of column A.

so output should be

Sheet 2:


A1 22

A2 2

A3 3

Has anyone worked with such a requirement?


share|improve this question

migrated from Oct 7 '12 at 11:10

This question came from our site for professional and enthusiast programmers.

This would be a good use for a pivot table. Using SUMIF won't reduce it to only unique values of A. – Jesse Oct 5 '12 at 0:21
up vote 0 down vote accepted

in sheet 2 place the following formula into cell B2 and drag down:



This says to check if column A from sheet1 has the key to my left.

If it does then return the sum of all the corresponding cells in column B that meet the requirement.

share|improve this answer
Awesome. It works, thanks much! – user726579 Oct 4 '12 at 21:54

You must log in to answer this question.