# Two Excel Tables

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?

-

## migrated from stackoverflow.comJan 17 '13 at 12:08

This question came from our site for professional and enthusiast programmers.

You can use `SUMIF()` for this – Tim Williams Jan 11 '13 at 17:18
Use a Pivot Table ;) Step by Step Tutorial : spreadsheets.about.com/od/datamanagementinexcel/ss/… – Siddharth Rout Jan 11 '13 at 17:23

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):-

-
Nice detailed answer displaying both options. +1! – Oldskool Jan 11 '13 at 19:08