Solved Excel 2003 - COUNTIF w/ multiple criteria

Microsoft office Excel 2003 (full produc...
October 8, 2010 at 15:26:01
Specs: Windows XP Professional 2002, Intel Core2 Duo E6550@2.33GHz/1.96GB RAM
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


See More: Excel 2003 - COUNTIF w/ multiple criteria

Report •


✔ Best Answer
October 9, 2010 at 10:42:35
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/



#1
October 8, 2010 at 16:22:52
If you have Excel 2007 try this =COUNTIFS() function

=COUNTIFS(B1:B6,"aru",C1:C6,">=10/03/2010",C1:C6,"<=11/03/2010")

If you have 2000 or 2003 try this ARRAY formula:

=COUNT(IF(B1:B6="aru",IF(C1:C6>=DATEVALUE("10/3/2010"),IF(C1:C6<=DATEVALUE("11/3/2010"),))))

As it is an ARRAY formula you can not use just ENTER, you must use CTRL-SHIFT-ENTER

MIKE

http://www.skeptic.com/


Report •

#2
October 9, 2010 at 08:56:06
Thank you for replying so quickly! Unfortunately, I need it to work across approximately 28 sheets (as this is going on my summary sheet). When I replace your cell ranges with the named range that holds my sheet names, I get a 0 - which, while better than the error messages I'd been getting trying to make this work on my own, is still not the correct answer. :-P Do you have any suggestions for making this work across a range of sheets?

Report •

#3
October 9, 2010 at 09:46:33
Do you have any suggestions for making this work across a range of sheets?

Since I don't know which formula your using:

This =COUNTIFS() function works for me.

=COUNTIFS(Sheet1!B1:B6,"aru",Sheet1!C1:C6,">=10/03/2010",Sheet1!C1:C6,"<=11/03/2010")

and

This ARRAY formula:

=COUNT(IF(Sheet1!B1:B6="aru",IF(Sheet1!C1:C6>=DATEVALUE("10/3/2010"),IF(Sheet1!C1:C6<=DATEVALUE("11/3/2010"),))))

works also.

If your using the ARRAY formula and your getting a return of zero, you probably did not us CTRL-SHIFT-ENTER.

The formula should have curly braces surrounding it when your done. It should look like:

{=COUNT(IF(Sheet1!B1:B6="aru",IF(Sheet1!C1:C6>=DATEVALUE("10/3/2010"),IF(Sheet1!C1:C6<=DATEVALUE("11/3/2010"),))))}

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
October 9, 2010 at 10:28:46
I'm using the ARRAY formula for Excel 2003 with the exception that instead of Sheet1, I have a set of sheets (dates 10-7 through 11-3) that I've put into a named range called P11Dates; I'm looking to count all instances across all sheets without having to count on each individual sheet first. And yes, I made sure to use ctrl+shift+enter.

Report •

#5
October 9, 2010 at 10:42:35
✔ Best Answer
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/


Report •

#6
October 9, 2010 at 11:35:25
Just as a note, =COUNT() won't span sheets, so while this will work:

=SUM(Sheet1:Sheet20!D1)

something like this won't work:

=COUNT(IF(Sheet1:Sheet20!B1:B6="aru",IF(Sheet1:Sheet20!C1:C6>=DATEVALUE("10/3/2010"),IF(Sheet1:Sheet20!C1:C6<=DATEVALUE("11/3/2010"),))))

MIKE

http://www.skeptic.com/


Report •

#7
November 5, 2010 at 23:33:22
Hi,

This is kind of close to the issue I'm having...

This is the data :

Sheet1
(Column) A B C D

Tania 16:00 22:00
Dinesh C 09:30 14:30
Babs C 09:00 17:00
Sejal C 10:30 19:00
Prerna 15:00 20:00
Tee
Lisa C 11:00 16:00
Jane
Tatum L 08:00 16:00

Sheet2
(Column) A

07:00
08:00
09:00
etc to 22:00

I want it to count for each hour people who have "C" in column B and who started (column C) on or before that hour but haven't finished (column D).

So I have an expected head count.

I can get everyone with using 07:00 as an example (so A1 on sheet2)

=COUNTIF(Sheet1!$C$1:Sheet1!$C$100,"<="&A1)-COUNTIF(Sheet1!$D$3:Sheet1!$D$100,"<="&A1)

and I've been trying for hours to get it to only count the ones with C and not everyone but everything seems to give weird numbers or just error.

Please help!

Thanks


Report •


Ask Question