Sum cell values into another sheet IF values in cells from two sheets match

For simplification I have two sheets in a workbook. I have a product ID in col A for both sheets.

In Sheet(1) I have a Qty ordered adjacent to the product ID. I wish to sum all the Qty(s) for a product ID into sheet(2) in the cell adjacent to the same product ID.

The selection of data in Sheet(1) will change with each processed order that is appended to the first blank row. The solution will need to expand dynamically with data added to sheet(1) and include the new data into the total of the static sheet(2).

MANY THANKS!!!


Hopefully this helps with clarity:

sheet1.PNG

Sheet2.PNG

1) To ThisWorkbook code module

Please Login or Register  to view this content.

2) To a Standard code module

Please Login or Register  to view this content.

3) Sheet2 code module

Please Login or Register  to view this content.

Will update automatically.

Added reputation for your answer, thank you. I was unable to get the code to work for me however. Are there areas in your code other than the sheets("name") that I should change to suit my needs?

Added reputation for your answer also Jindon. However, I was unable to get the code to work for me. Can you explain what changes I should make to allow it to work on my sheets, or explain how the three separate modules are supposed to work together?

Thank you also

Upload your workbook.

Workbook attached. I've left the tab names as they are for the real sheet.

What the code should do is look at the "Historical" tab and add all the Qty cells for a single product ID then place that Sum into the "Tallies" sheet in the adjacent cell to the same product ID in the Count column.

The historical tab will change throughout each month as more data is added, and the tallies sheet will need to update with the updated data to create a total of all product IDs ordered in the Historical tab and sum them in the tallies sheet for the same ID.

No need of vba.

Formula: C2 in 'Tallies' sheet

=SUMIFS(Historical!C:C,Historical!A:A,A2,Historical!B:B,B2)

then copy down.

Beautiful, thank you again Jindon!

You are welcome and thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.