How to add up numbers across multiple rows and sheets with specific identifiers?

Question

I have the following setup in Microsoft Excel. I have multiple sheets and each of them has two columns. Let's say one column is some form of identification (like ID number) and the other is some numeric content (say amount of money). It is possible for a sheet to have multiple rows with the same ID, and for the same ID to appear in multiple sheets. For example:

SHEET 1

ID  Amount
1       10
2       20
1       60

SHEET 2

ID  Amount
1       30
2       50
1       40

I'd like to create another sheet that sums the total amount of money of each ID number:

SHEET: SUMMARY

ID  Amount
1      140
2       70

I guess this has to do with creating a pivot table, but it requires each sheet not to have rows with the same ID. Could you please give me some guide?

Answer 1

Actually it's very simple.

SUMIF(Sheet1!$A$1:$A6, $A3                , Sheet1!$B$1:$B6)
      Range of ID    , ID on Summary Sheet,  Range of Amount 

This formula sums all Amount with ID on Sheet1 matching the value of A3 on Summary sheet.

Your summary table should look like this

ID    | Amount
1     | =SUM( SUMIF(...)+SUMIF(...)+SUMIF(...)+... )
2     | =SUM( SUMIF(...)+SUMIF(...)+SUMIF(...)+... )

and change to formula above for all the seven sheets you need.

You can autofill the formula, but remember the $ signs on the formula!