Summing items in a table matrix form

[kgallo]

I have a spreadsheet that has various columns titled with a date (last day of each month) and this is populated each month that passes. Each row has a code and these can duplicate. What I need to do is sum all the same codes together for a specific month. But I need this to be automatic where I don't change it each month manually. I have a tab that just has some variables which are the current month and the prior month data. So off of this tab I know which month (column) to look for in the table and hence pull the data. My issue is locating that column. I have tried using a pivot table and then a vlookup and it works great if I hardcode the column to pull the data from. But I don't want to hardcode this as I just want to put in the current month in the variables tab and have this populate automatically. And referencing a date in the pivot table was not working for me. So then I was thinking of using a Sumif function and having an additional row at the top with a 1 (conditional formula) if that is the current month. But I still need to be able to locate which exact column has the 1 in it and then reference that in the sumif. How would I do that necessarily? R1C1 format, but exactly how? I could use the index function to find the location of the 1 in the row, but then need a way to tell the sumif function which column is the 6th column in the spreadsheet for example.
So the problem is like this:

On one spreadsheet I have this table:

11/30/13 12/30/13 1/31/14 2/28/14 3/31/14 .... and can go all the way out until 12/31/16 say
ABC 5 8 3 7 5
CDF 10 3 4 1 3
ABC 12 2 5 1 2
GHI 3 15 8 3 5
XYZ 2 7 9 5 5
GHI 1 6 10 2 3

On another spreadsheet I have this information:

Current month 3/31/14

And then on other spreadsheets I am looking to gather data from the 1st spreadsheet in a format such as this:
Current Month Prior Month
ABC 7 8
CDF 3 1
GHI 8 5
XYZ 5 5

So I need these to be sums and need to be able to pull from just that one particular column. If anyone has some suggestions PLEASE let me know!! I have been puzzled on this for hours!!
Thank you soooooooooooo much in advance!!

[BadlySpelledBuoy]

Post an example workbook and I'll show you how.

[kgallo]

Ok great. I created a sample workbook in excel and tried to attach it but am not sure how to here. Can you tell me? sorry I am new to this. Thank you!

[kgallo]

Ok. I got it. here is the sample - thank you!

[BadlySpelledBuoy]

Happy to help.

Don't forget you mark the thread as SOLVED if you're satisfied you have your solution.

BSB.

[BadlySpelledBuoy]

Try the below formula in cell D8 of the Summary worksheet. You can then copy it to the other relevant cells and it should produce what I think you mean.

=SUMPRODUCT((source!$D$4:$Q$4=D$6)*(source!$A$6:$A$41=$A8),source!$D$6:$Q$41)

[kgallo]

Yes this works great - thank you!!