Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

migrated from Jan 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 :… – 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':

SO14283233 example

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

enter image description here

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

You must log in to answer this question.