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
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/