Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004

    Unanswered: Dcount Alternative?


    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

    Last edited by Zygorf; 04-21-04 at 07:13.

  2. #2
    Join Date
    Apr 2004

    Re: Dcount Alternative?

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

  3. #3
    Join Date
    Oct 2003
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts