Countif looking at many sheets
 

How can I adapt the following to also look in the same range in sheet1!,
sheet2! and sheet3! as well as sheet 4! ?
=COUNTIF(sheet4!Z$4:Z$4000,"b")
--
Traa Dy Liooar

Jock

Countif looking at many sheets
 

=SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3,4}&"!Z$4:Z$40 00"),"b"))

"Jock" wrote:

How can I adapt the following to also look in the same range in sheet1!,
sheet2! and sheet3! as well as sheet 4! ?
=COUNTIF(sheet4!Z$4:Z$4000,"b")
--
Traa Dy Liooar

Jock

Countif looking at many sheets
 

Remove the $ sign in the data range

=SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3,4}&"!Z4:Z4000 "),"b"))


"Teethless mama" wrote:

=SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3,4}&"!Z$4:Z$40 00"),"b"))

"Jock" wrote:

How can I adapt the following to also look in the same range in sheet1!,
sheet2! and sheet3! as well as sheet 4! ?
=COUNTIF(sheet4!Z$4:Z$4000,"b")
--
Traa Dy Liooar

Jock

Countif looking at many sheets
 

All I get is #REF!
All sheets have names - would that make it easier?
--
Traa Dy Liooar

Jock


"Teethless mama" wrote:

Remove the $ sign in the data range

=SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3,4}&"!Z4:Z4000 "),"b"))


"Teethless mama" wrote:

=SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3,4}&"!Z$4:Z$40 00"),"b"))

"Jock" wrote:

How can I adapt the following to also look in the same range in sheet1!,
sheet2! and sheet3! as well as sheet 4! ?
=COUNTIF(sheet4!Z$4:Z$4000,"b")
--
Traa Dy Liooar

Jock

Countif looking at many sheets
 

put all your sheet names in the cell:
A1: test 1
A2: test 2
A3: test 3
A4: test 4

"Mysheets" is defined name range A1:A4 (no quotes)

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!Z4:Z4 000"),"b"))


"Jock" wrote:

All I get is #REF!
All sheets have names - would that make it easier?
--
Traa Dy Liooar

Jock


"Teethless mama" wrote:

Remove the $ sign in the data range

=SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3,4}&"!Z4:Z4000 "),"b"))


"Teethless mama" wrote:

=SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3,4}&"!Z$4:Z$40 00"),"b"))

"Jock" wrote:

How can I adapt the following to also look in the same range in sheet1!,
sheet2! and sheet3! as well as sheet 4! ?
=COUNTIF(sheet4!Z$4:Z$4000,"b")
--
Traa Dy Liooar

Jock

Countif looking at many sheets
 

That's it, thank you.
--
Traa Dy Liooar

Jock


"Teethless mama" wrote:

put all your sheet names in the cell:
A1: test 1
A2: test 2
A3: test 3
A4: test 4

"Mysheets" is defined name range A1:A4 (no quotes)

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!Z4:Z4 000"),"b"))


"Jock" wrote:

All I get is #REF!
All sheets have names - would that make it easier?
--
Traa Dy Liooar

Jock


"Teethless mama" wrote:

Remove the $ sign in the data range

=SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3,4}&"!Z4:Z4000 "),"b"))


"Teethless mama" wrote:

=SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3,4}&"!Z$4:Z$40 00"),"b"))

"Jock" wrote:

How can I adapt the following to also look in the same range in sheet1!,
sheet2! and sheet3! as well as sheet 4! ?
=COUNTIF(sheet4!Z$4:Z$4000,"b")
--
Traa Dy Liooar

Jock

Countif looking at many sheets
 

You're Welcome!


"Jock" wrote:

That's it, thank you.
--
Traa Dy Liooar

Jock


"Teethless mama" wrote:

put all your sheet names in the cell:
A1: test 1
A2: test 2
A3: test 3
A4: test 4

"Mysheets" is defined name range A1:A4 (no quotes)

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!Z4:Z4 000"),"b"))


"Jock" wrote:

All I get is #REF!
All sheets have names - would that make it easier?
--
Traa Dy Liooar

Jock


"Teethless mama" wrote:

Remove the $ sign in the data range

=SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3,4}&"!Z4:Z4000 "),"b"))


"Teethless mama" wrote:

=SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3,4}&"!Z$4:Z$40 00"),"b"))

"Jock" wrote:

How can I adapt the following to also look in the same range in sheet1!,
sheet2! and sheet3! as well as sheet 4! ?
=COUNTIF(sheet4!Z$4:Z$4000,"b")
--
Traa Dy Liooar

Jock

Countif looking at many sheets
 

look in the same range in sheet1!, sheet2!
and sheet3! as well as sheet 4!

If you only have a "few" sheets...

=COUNTIF(Sheet1!Z$4:Z$4000,"b")+COUNTIF(Sheet2!Z$4 :Z$4000,"b")+COUNTIF(Sheet3!Z$4:Z$4000,"b")+COUNTI F(Sheet4!Z$4:Z$4000,"b")

--
Biff
Microsoft Excel MVP


"Jock" wrote in message
...
How can I adapt the following to also look in the same range in sheet1!,
sheet2! and sheet3! as well as sheet 4! ?
=COUNTIF(sheet4!Z$4:Z$4000,"b")
--
Traa Dy Liooar

Jock