Excel 2007 : Need assistance with IF and possibly SUMIF Functions

Hi,

I'm currently working on a spreadsheet for some validation records. My problem is that I have multiple variables involving different weeks and different values.

So what I need is to figure out a way to calculate the amount of records that fall within a specific date range and that display the value Y. I've tried using Countif but that's too simple. IF function might do but I need to figure out a way to display records that fall in between 7/1/2012 and 7/6/2012. I was also informed that the ETA dates change regularly (weekly basis), so selecting individual cells with the dates won't work.

Probably the toughest design I've taken on since college.


Example.xlsx

Thanks for the help.

Does anyone have any ideas how I can do this?

Thanks