COUNTIF w/ multiple sheets & criteria

**rubykavalier** · 10-11-2010, 06:29 PM

Hello all,

I'm desperately hoping someone can help me with this. I'm wrestling with a formula in Excel 2003 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've put into 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:

HTML Code:

   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 (or sixteen, if there were 8 identical sheets).

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

--Ruby

**JBeaucaire** · 10-11-2010, 06:51 PM

The EXCEL AddIn MoreFunc has a function called THREED() you could install and try.

A little tougher to try without. Put the sheet names you want to collect data from on this summary sheet in cells AA1:AA8 (or however far down you need).

Sheet1
Sheet2
Sheet3
Sheet4
Sheet5
Etc...

Then put:
A2 = ARU
B2 = Start Date
C2 = End Date
D2 gets the following formula:

=SUMPRODUCT(--(T(INDIRECT("'" & $AA$1:$AA$8 & "'!B1:B100"))=$A2), --(N(INDIRECT("'" & $AA$1:$AA$8 & "'!C1:C100"))>=$B2), --(N(INDIRECT("'" & $AA$1:$AA$8 & "'!C1:C100"))<=$C2))

**JBeaucaire** · 10-11-2010, 07:02 PM

I know that syntax is close, but it's not exactly right for some reason. Still thinking it over...

**rubykavalier** · 10-11-2010, 07:57 PM

Unfortunately, I'm not able to download add-ins for this project. Question: is there any way to do this referencing the criteria directly instead of cells containing the information? I ask because there's a total of about... 20 sites and 17 different date ranges I'm going to need to check per site, and it would be easier for me to edit the criteria I'm looking for in the formula directly.

**rubykavalier** · 10-11-2010, 09:50 PM

Additionally, your formula seems to work for the first site (ARU) and sets of dates, but when I change the cell reference to count another site, I only get 0 instead of the actual count. Any idea what might be causing that?

**Beep13** · 11-23-2010, 01:13 PM

I've been hung up on this exact problem for a while. Just found a solution.

The problem below is that you are only returning one value per sheet. So if a sheet has multiple of the same VALUE, it will not be counted. Also, in reality, the formula is only searching the first cell on each sheet. So only the values in B1 are counted. That's why if you change the criteria to "bbb" you get 0. There are "bbb" in B1 on any sheet.

There is only one solution that I know of, and its rather simple.

For excel 2007 you need to use a combination of SUMPRODUCT(COUNTIF()) to seach through mulitple sheets for criteria. You can use COUNTIFS for multiple If statements for your product.

The other trick is, COUNTIF/COUNTIFS only checks for equal values.
To search for somehting greater/less than, ectd, try to following mod:

COUNTIF(range_of_data,$A2)
COUNTIF(range_of_data,">="&A2)

Put this all together and use the following formula.
The best part is, with SUMPRODUCT there is no need to convert text/number ectd.

Here is the result:

=SUMPRODUCT(COUNTIFS(INDIRECT("'" & MySheets & "'!$B1:$B100"),$A2,INDIRECT("'" & MySheets & "'!$C1:$C100"),">="&$B2,INDIRECT("'" & MySheets & "'!$C1:$C100"),"<="&$C2))