computing
  • 0

Solved Excel 2003 – COUNTIF w/ Multiple Criteria

  • 0

Hello all,

I’m desperately hoping someone can help me with this. I’m wrestling with a formula to count cells over multiple sheets with multiple criteria. For example, I have a workbook with one tab for each date in our sales period that I’m using as a named range (&P11Dates;&). On each sheet, I’ve got a column with a three-letter abbreviation (ARU, for example), and in another column I’ve got dates. So:

 

   A        B         C
1234567    ARU    10/7/2010
1234567    DES    10/9/2010
1234567    ARU    10/8/2010
1234567    SIN    11/3/2010
1234567    ARU    11/4/2010
1234567    HAW    10/3/2010

What I’m looking to do is count, across all sheets in my named range, the number of times a row both contains ARU in column B and where column C falls between 10/7/2010 and 11/3/2010 inclusive. So in the above example, the formula should return 2.

If there’s a way to do this, I’d be supremely grateful.

–Ruby

Share

1 Answer

  1. Simplest way I can think of is to add the formula to each sheet, then total the sheets

    So if the ARRAY formula is in D1 on each of the sheets, on your summary sheet something like

    =SUM(Sheet1:Sheet20!D1)

    Will give you a total count of all the sheets.

    There is probably a way of using =INDIRECT() to get all the sheet names, then add them into a formula for each sheet, then do a sum.

    Also, there is probably a VBA solution, but my VBA skills are just about nill.

    MIKE

    http://www.skeptic.com/

    • 0