Count Values between dates and multiple criteria

Hi All,

I am trying to create a excel dash board that will show my work for the past week. I have 2 sheets, the dashboard (sheet 1) and the raw data sheet 2.

Summary

1)

In sheet 2 I have a column of case ID's (A) and adjacent to them I have a column of a "submit date" (B)

What I need to do is count all the the cases that have a submit date of between 9-29-2013 and 10-5-2013. I should be able to drag this formula to the adjacent cell so
it will calculate all the cases that have been submitted next week as well.

2)

I need a count of all the case id's that are between 9-29-2013 through 10-5-2013 in the UPDATE DAte column (column C) AND the status column is "closed"

3)

I need a count of all the cases from 01-01-2011 through 10-5-2013 and the status column is NOT closed.

Any help will be greatly appreciated!!

I have attached a sample work book as well