COUNTIF multiple sheets

**Schalk** · 11-17-2011, 02:42 AM

Hi all!

How do I count the same range om several sheets?

Eg:

I want to count L4 to L8 on sheets 1 to 31,
by modifying this formula:
=COUNTIF('[DAILY REPORTS AND ROUTES.xlsx]1'!$L$4:$L$8,"CLIENT")

I have tried:
=COUNTIF('[DAILY REPORTS AND ROUTES.xlsx]1:31'!$L$4:$L$8,"CLIENT")
but it doesnt work.

Please help!

**ConneXionLost** · 11-17-2011, 03:40 AM

Although Excel allows the range notation to span several sheets for the same range, it's still the same as several ranges. Unfortunately, the COUNTIF function does not support multiple ranges.

Cheers,

**Schalk** · 11-17-2011, 03:48 AM

Any other way of doing it then?

**ConneXionLost** · 11-17-2011, 03:51 AM

Use the COUNTIF in each sheet (in a helper cell), then sum those cells.

**ConneXionLost** · 11-17-2011, 03:53 AM

Or, build a large formula like:

Please Login or Register  to view this content.

**vlady** · 11-17-2011, 03:54 AM

Maybe if possible...!!??

DEFINEDSHEET - defined name which holds the sheet names

Please Login or Register  to view this content.

**Schalk** · 11-17-2011, 04:09 AM

SJEEZ NOW U HAVE MY HEAD SPINNING!!!

Thanx!

**Schalk** · 11-17-2011, 04:16 AM

Say I want to count sheets 1-31, how would I write this then?

=SUMPRODUCT(COUNTIF(INDIRECT("'"&DEFINEDSHEET&"'!$L$4:$L$8"),"CLIENT"))
tried
=SUMPRODUCT(COUNTIF(INDIRECT("'"&DEFINEDSHEET1:31&"'!$L$4:$L$8"),"CLIENT"))
doesnt work...

**kvsrinivasamurthy** · 11-19-2011, 06:33 AM

In the workbook DAILY REPORTS AND ROUTES.xlsx The range L4:L8 in Each Sheet can be Named like
SL1,SL2,....etc and following formula can be used
=SUM(COUNTIF(SL1,"CLIENT"),COUNTIF(SL2,"CLIENT"),COUNTIF(SL3,"CLIENT"),......)