Adding value of one cell with previous value of same cell if value of another cell matches in Excel

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.

microsoft-excel
worksheet-function
counter

user726579

Posted 2012-10-04T21:45:28.263

Reputation:

This would be a good use for a pivot table. Using SUMIF won't reduce it to only unique values of A. – Jesse – 2012-10-05T00:21:29.617

Answers

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.

danielpiestrak

Posted 2012-10-04T21:45:28.263

Reputation: 411