Personally, I'd use a pivot table for this. Your data is set up
appropriately and you have a lot of flexibility on what would be
included in the table. For instance, as new departments or locations
are added to the data, you could just refresh the pivot table and they
would be automatically added. Follow the pivot table wizard to get
started.

If, however, you want to manually create your second table, try this.

In Sheet2 assume the dept names start in cell B1 and work to the right.
Also assume the locations start in cell A2 and work down. In cell B2,
enter the following:

=SUMPRODUCT(--(Sheet1!$B$2:$B$1000=B$1),--(Sheet1!$A$2:$A$1000=$A2),Sheet1!$C$2:$C$1000)

Note the position of the dollar signs. This is critical to allow you
to copy this formula throughout your table without editing.

Good luck

- John Michl