Excel SUM from two tables based on ID in both tables

Question

I have 2 tables in Excel:

Table1 with ID, PointsA ... &
Table2 with ID, PointsB ...

ID from Table1 and Table2 have duplicates and some in Table1 are in Table2 and vice versa but not all of them. I want a sort of table that combines these two tables and show a list of unique IDs with SUM of PointsA and SUM of PointsB.

e.g. I have:

| ID | PointsA |  
| A  | 3       |  
| C  | 1       |  
| A  | 1       |

and

| ID | PointsB |
| C  | 2       |
| B  | 2       |
| C  | 1       |
| A  | 0       |

I want:

| ID | PointsA |  PointsB | 
| A  | 4       | 0        | 
| B  | 0       | 2        | 
| C  | 1       | 3        | 

How do I go about doing this? Each table has about 25000 items and 30 columns. (Ideally I want a pure excel solution and not use Macro.)

I've tried creating a pivot table that combines the two tables, but I can't seem to find a way to treat ID in Table1 and ID in Table2 as the same thing. I tried creating a relationship but this wouldn't work because of duplicate values.

Answer 1

SUMIF() will give you your desired results.

I made this example, which shows what to do (note, you might need to use a ,instead of a ; in your equation.