| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using COUNTIF across different sheets | Excel Discussion (Misc queries) | |||
| COUNTIF across multiple sheets | Excel Worksheet Functions | |||
| COUNTIF across sheets | Excel Worksheet Functions | |||
| COUNTIF across all sheets of a workbook | Excel Worksheet Functions | |||
| COUNTIF across all sheets of a workbook | Excel Worksheet Functions | |||
Linear Mode
