Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Here is my data in sheet1 of excel:


A B


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:


A B


A1 22


A2 2


A3 3


Has anyone worked with such a requirement?

thanks.

share|improve this question

migrated from stackoverflow.com 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

1 Answer 1

up vote 0 down vote accepted

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

=SUMIF(Sheet1!A:A,Sheet2!A2,Sheet1!B:B)

Details

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.