# COUNTIF multiple sheets

1. ## COUNTIF multiple sheets

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.

2. ## Re: COUNTIF multiple sheets

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,

3. ## Re: COUNTIF multiple sheets

Any other way of doing it then?

4. ## Re: COUNTIF multiple sheets

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

5. ## Re: COUNTIF multiple sheets

Or, build a large formula like:

``Please Login or Register  to view this content.``

6. ## Re: COUNTIF multiple sheets

Maybe if possible...!!??

DEFINEDSHEET - defined name which holds the sheet names

``Please Login or Register  to view this content.``

7. ## Re: COUNTIF multiple sheets

SJEEZ NOW U HAVE MY HEAD SPINNING!!!

Thanx!

8. ## Re: COUNTIF multiple sheets

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

9. ## Re: COUNTIF multiple sheets

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"),......)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1