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

✔ 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

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

mustuse CTRL-SHIFT-ENTERMIKE

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?

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

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.

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

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

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

This is the data :

Sheet1

(Column) A B C DTania 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:00Sheet2

(Column) A07:00

08:00

09:00

etc to 22:00I 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

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History