To reference the city in order to reference the proper sheet, you will need to incorporate the INDIRECT function like this:

=SUMPRODUCT(--(LEFT(INDIRECT("'[YTD_District (2011).xls]"&A13&"'!$A$2:$A$59"),2)=$C$4),(INDIRECT("'[YTD_District (2011).xls]"&A13&"'!$G$2:$G$59")))

but note that INDIRECT only works with open workbooks.

If you want to work with closed workbooks, you will need to download and install a free addin from here and replace INDIRECT with INDIRECT.EXT