Excel 2007 / Sumproduct Help

[cobra35y]

Good day, I am using excel 07. I have a spreadsheet in which I have 3 columns that I need each row totaled from another sheet.
Situation: I need the total number of occurrences that "AT" appears on another sheet "sheet2" in column "B" within the designated rows "B3:B5000".
On "Sheet1" I have the reference date ranges in each row. The total occurrences will be in column "C" in which the cell "C1" has the referene text "AT*" that I need as the pre-text designator. The purpose in the asterisk * is to find all occurrences beginning with "AT" within each of the date ranges listed on "Sheet1" columns A and B.
Sheet1:
Column "A" header is "start date"
Column "B" header is "end date"
Column "C" header is "AT*"

Sheet2:
Column "B" header is "date Received" formatted as "6-Mar-11"
Column "C" header is "work order #" formatted as "general"

The workorder #'s consist of over 1000 rows each with different text/number combinations occurring on different dates. Example: "AV8654301", "AT8996532", etc...

On sheet 1 column "C" cells 1-52 equaling each date range total occurrences of "AT", etc...

Any help is appreciated.

This is one of the formulas I tried that works for obtaining the total number of rows occurring between dates.

=sumproduct(-('historical closed'!c3:c5000>b4), -('historical closed'!c3:c5000<c4))

Thanks in advance...

 

[cobra35y]

*Bump*

 

[cobra35y]

Curious if this too complex? I have searched multiple sites the last few days have tried many combinations of formula with no success. I don't just post without researching my question in depth. I need help if anyone can take a try at this... I can post a sample if needed, however I am using my phone for the search.

Again thanks to everyone in advance for your help.

 

[barry houdini]

Try using COUNTIFS, e.g. in C2 copied down

=COUNTIFS(Sheet2!B:B,">="&A2,Sheet2!B:B,"<="&B2,Sheet2!C:C,C$1)