Formula for multiple matches to sum result from another column into another sheet

I have spent so much time trying to figure this out on my own with a spectacular lack of success

I want to lookup a number of codes and then sum the balances from another column if code matches are found. There are 12 codes that I want to combine into one cell on another sheet in the same workbook. The data is exported from another program and I want to avoid having to manipulate that in any way but would just like to paste in into excel and have excel do all the work. Each month I need to compare results for the same period last year so I need this formula to work on freshly pasted data. The same number of accounts may not have a balance in column C each month but when any of the 12 account codes do appear I need the formula to identify if the code is there and then sum the number in col c into cell G7 on Jul 13 sheet. Each month I will have a new sheet in the same workbook for current month and LY month. The 12 old codes need to be combined for the new code 6-2440 shown on consolidation of gl codes sheet.

The uploaded example is a snapshot of part of the file and the columns that are blank are populated in the real file so need to be left alone.

Jul 13 cell G8 should show total $53,161.87 which is the combined total of the following codes found on LY July Data sheet
6-2231 $9,803.70
6-2232 $3,650.08
6-2233 $25,769.22
6-2234 $17,307.70
6-2235 $2,769.24
6-2273 $1,237.50
6-2599 ($7,375.57)

I created a formula that got ridiculously long when I had to combine 6 codes in another section of the spreadsheet and I am sure that there are much better ways to achieve the same result but to my utter amazement it does work! Formula is shown below, please be kind and don't laugh too loud Clearly as you can see I am in need of some expert help!

=SUMPRODUCT(IF(ISNA(VLOOKUP($A78,'LY Jul Data'!$A:$F,3,0)),0,VLOOKUP($A78,'LY Jul Data'!$A:$F,3,0)))+(IF(ISNA(VLOOKUP('Consolidation of gl codes'!A49,'LY Jul Data'!A:F,3,0)),0,VLOOKUP('Consolidation of gl codes'!A49,'LY Jul Data'!A:F,3,0))+(IF(ISNA(VLOOKUP('Consolidation of gl codes'!A50,'LY Jul Data'!A:F,3,0)),0,VLOOKUP('Consolidation of gl codes'!A50,'LY Jul Data'!A:F,3,0))+(IF(ISNA(VLOOKUP('Consolidation of gl codes'!A51,'LY Jul Data'!A:F,3,0)),0,VLOOKUP('Consolidation of gl codes'!A51,'LY Jul Data'!A:F,3,0))+(IF(ISNA(VLOOKUP('Consolidation of gl codes'!A52,'LY Jul Data'!A:F,3,0)),0,VLOOKUP('Consolidation of gl codes'!A52,'LY Jul Data'!A:F,3,0))+(IF(ISNA(VLOOKUP('Consolidation of gl codes'!A116,'LY Jul Data'!A:F,3,0)),0,VLOOKUP('Consolidation of gl codes'!A116,'LY Jul Data'!A:F,3,0)))))))

Note the formula must work in Excel 2003 please.

Hoping someone can save my sanity

g'day ratcat

Thanks so much for your assistance, much appreciated! Please see the attached as there is something not quite right with the formula as it is returning the figure in the row below and I'm not sure why? (see sheet attached as I have added column that shows the correct result). Also would it be possible to have the formulas in col H on sheet LY Jul Data in the Consolidation of gl codes sheet instead as that one remains unchanged each month whereas there will be a new LY sheet each month?

Many thanks

Thanks ratcat, this works perfectly! I see now why it wasn't returning the correct result. I have copied this into my spreadsheet for the first 3 months of the year and it works just perfectly This will be such a time saver so thank you sooooooo much

PS I did reply earlier before closing the thread but somehow that reply didn't get posted