Hi

I currently have 2 excel spreadsheets named Team 1 master and team 2 master. Each spreadsheet has on it a list of approx 5000 items. I have column headings as follows:

Date, CSC, Team Manager, Plan Number, CR Number, Deadline Date, Green/Red SLA, Dept Sent to, Callback required, Promise met, Correct CR Raised, and a few more which are not used in the counts I need.

What I need is a count of the number of requests where the Date is between 2 fixed dates (1 week), the green/red SLA is green, and the Team manager is a certain team manager. I need this result for 10 weeks. These Results are to be shown on a third spreadsheet.

I have looked at using dcount but this means having 10 different criteria arrays. I have also looked at using array formulae but I want to avoid this is this drastically slows down the spreadsheet. Is there any other way to do this? Possibly in VBA?

I have attatched a small sample of the spreadsheet

Thanks

where's the attachment? Have you ziped it before submit?

SUMPRODUCT is one way to do it.

I put start date in L1 and End date in M1, then used this formula (it works). Adjust ranges as needed.

=SUMPRODUCT(($A$2:$A$100>=L1)*($A$2:$A$100<M1)*($C $2:$C$100="Joe")*($G$2:$G$100="green"))

If you need this one the summary page, then include the sheet reference with each range. be sure not to use A:A with sumproduct, it won't work.