Two Excel Tables

Question

I have a table as below.

Airport name       U.S.D 
ALY             111,447.70 
ALY             559,557.23 
LXR              12,827.16 
LXR              79,720.62 
HRG               1,353.77 
HRG           1,187,334.08 
SSH               8,417.65 
SSH           2,900,602.08 
TCP               6,363.41 
TCP              90,279.68 
TCP              18,567.52

In another table I would like the sum of the amounts from the first table by specific airport:

airport name  total Amount of each airport 
ALY             671,004.93 
LXR             292,547.78 
HRG           1,188,687.85 
SSH           2,909,019.73 
TCP             115,210.61 

How do I make the second column in the second table calculate the SUM automatically from the first table?

Answer 1

Of course Tim Williams and Siddharth Rout are both correct (they're never not!) but I'd personally prefer the pivot table option because it is so versatile. To adapt general guidance to our specific requirement:-

Select your first table, Insert, Tables, PivotTable, Pivot Table, OK, click inside the box, from Choose fields to add to report: drag 'Airport name' into Row Labels and 'U.S.D' into Σ Values and, basically, 'your are done':

The default is for the pivot table to be on a new sheet, but as in example it can be placed in the same sheet (in Create Pivot Table, select Existing Worksheet and a suitable Location:). I have also formatted the values (left-click on Sum of U.S.D, select Value Field Settings and choose) and have departed from a strict alphabetical sort of Row Labels (Airport names) with right-clicking LXR in the pivot table, Move and Move "LXR" Up.

---

An example of using =SUMIF (Tim Williams' suggestion):-