Multiple Criteria for COUNTIF / IF / AND..in excel 2003!

Good morning everyone!

Hopefully you can help! I've been stuck on this one problem for over a week - and usually I can sort out my problems by searching all the threads here, but this has proved to be a fruitless serach this time, as nothing seems to work!

Basically, I have a spreadsheet for work, where we maintain a database of the validity of peoples checks on the teams. There are four teams, spread over four sheets, and the checks expiry date is tracked against the individual - there are 8 differing types of checks that we use. On the overview sheet (Front Cover) I have managed to complete the information for the Team breakdown (mainly thanks for people on here - Thanks guys!). Here is my issue :

Each team is broken down again, into synidcates. There are four syndicates (1-4). The syndicates are split up between the four teams, so when I need to display the information on the overview sheet, I need to search all teams coumn C2:C145 (I have simply gone to C145 incase we increase the size of the teams) of for their syndicate, and then display if their check is due within the next 4 weeks. I have managed to complete the "in date" and "out of Date" data, but for some reason, I cannot get the forumal to work with the "due in 4 weeks". Apparently it would be simple with COUNTIFs, but I'm using Excel 2003 - ARGH!

So, the formula needs to read C2:C145 from all sheets, checking which syndicate is displayed, and then decide if the relevant check is due within 4 weeks of todays date. I have tried having "cheat" boxes hidden away with all the information split down, but that doesn't seem to help. Apparently it may have to involve VBA, which would be a potential non-starter from me!

I've included a (very) stripped down example of what I am working on - The original contains a lot of sensitive data, so I mocked up what it would look like - enough for someone to understand what I mean / need.

I REALLY hope someone can help....PLEASE!!!! Many, many thanks everyone!