lookup values by date

I have a spreadsheet I'm trying to figure out the formula for.....

The spreadsheet include an account#, name of a trade show, the date of the trade show, and then every single month for the past 2 years or so. Under each month it tells me how many orders that particular account number has placed for that month. I would like to know see how many orders each account placed 3 months before show date, 3 months after show date, 6 months before show date, 6 months after show date, 12 months before show date, 12 months after show date.

For example the Show ASI Chicago 2009 took place 07/01/2009. See how many orders the account number ALLE43077 placed 3 months before 07/01/2009 (April 2009, May 2009 and June 2009) total into one cell, 3 months after 07/01/2009 (August 2009, September 2009 and October 2009) totaled into one cell

Thanks Richard! When I copy the formula down in my complete spreadsheet it doesn't work. I receive #Name? Also when I extend the formula to the right for +/- 6 months +/- 12 months, I thought I could just copy the formula over and change the -3s to -6s and -12s and keep everything else the same but its giving me #NAME? as a result. What am I doing wrong?

Hi ShaylaMc,

Richard's formula works really well in my computer, but then I noticed that you have an older version of Excel. SUMIFS formula only working in 2007+, not in 2003 version.

Try this formula instead...
=SUMPRODUCT(($J2:$BD2)*($J$1:$BD$1>=DATE(YEAR(EOMONTH($C2,-3)),MONTH(EOMONTH($C2,-3)),DAY($C2)))*($J$1:$BD$1<$C2))

Thanks for both helping. The new formula djapigo provided does not work. I do have excel 2003. I have attached an updated spreadsheet showing that I'm receiving #NAME?

Thanks again for the help!

Sorry, try this...

D2: =SUMPRODUCT(($J2:$BD2)*($J$1:$BD$1>=DATE(YEAR($C2),MONTH($C2)-3,DAY($C2)))*($J$1:$BD$1<$C2))

E2: =SUMPRODUCT(($J2:$BD2)*($J$1:$BD$1>=DATE(YEAR($C2),MONTH($C2)+3,DAY($C2)))*($J$1:$BD$1<$C2))

Hi Richard,

I wasn't sure either, but I took a shot and it seems the DATE function can read negative months and just remove it from years... go figure...