Data from two sheets to a third, then compare and add together if same

[dzig_33]

I have two sheets (named sheet1 and sheet2), column B is labelled $ and Column K is labelled Buyer. I would like to take the buyer names from column K of both sheets to sheet3 column A and the $ amounts from column B of both sheets to column B of sheet3, then if the names in column A are an exact match, add the $ amounts together, ending up with each name only listed once on sheet 3 column A and their total $ amount from sheet1 and sheet2 on the corresponding line in column B.

 

[Andrew Fergus]

Hello and welcome to MrExcel

Here's how to do this using native Excel functionality and formulas:

Copy the names from column K in sheet 1 into cell A2 on sheet 3.
Copy the names from column K in sheet 2 to the next unused cell in column A on sheet 3.
While on sheet 3, select cell A2, select menu option Data > Remove Duplicates > (tick header if you have a header value in cell A1) > tick Column A > Ok.

With this reduced list enter the following formula into cell B2:
=SUMIF(Sheet1!K:K,A2,Sheet1!B:B)+SUMIF(Sheet2!K:K,A2,Sheet2!B:B)
and copy down.

I trust this helps.
Andrew

 

[dzig_33]

Finally had some time to get back to this. I have it working as Andrew Fergus suggested. Many thanks. Now wondering if there is a way to make this more dynamic, meaning if I delete a name in column K in either of the first two sheets, it will update sheet 3. Might be getting in to more than I want to, but I thought I would throw this question out there.