Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with counting across worksheets
Hi!
If you use the default sheet names: Sheet1, Sheet2,=20 Sheet3, etc .... Assume you want to Countif B1:B10 equals 10 on sheets 2:10 =3DSUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIREC T("2:10")) &"!B1:B10"),10)) If you use custom sheet names .... List the sheet names in a range, say H1:H9 =3DSUMPRODUCT(COUNTIF(INDIRECT(H1:H9&"!B1:B10"),10 )) Biff -----Original Message----- Sorry in advance for what is likely a very basic=20 question, but I would=20 appreciate help. I have created a workbook with multiple=20 worksheets which=20 all have the same format, but different data. For=20 example, each row=20 represents a person, and each column represents a time=20 period. The row and=20 column headers are the same in each worksheet, but what=20 HAPPENED in the time=20 periods varies. I=E2?Tm trying to create a formula that=20 would look at the SAME=20 range in each worksheet and count how many times a=20 specific variable appears,=20 returning simply the number of occurrences. It would=20 seem to be a COUNTIF=20 formula, but that function doesn=E2?Tt seem to like 3-D=20 references. Any ideas????? . |
#2
|
|||
|
|||
Thank you Biff, that will help me in a project I will soon work on.
To make it more difficult, would it be possible to link books together in a format like that? Such as Patient 1011.xls, and Patient 1012.xls Pull particular information from those books into one easy to read sheet? Thank you in advance. "Biff" wrote: Hi! If you use the default sheet names: Sheet1, Sheet2, Sheet3, etc .... Assume you want to Countif B1:B10 equals 10 on sheets 2:10 =SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT( "2:10")) &"!B1:B10"),10)) If you use custom sheet names .... List the sheet names in a range, say H1:H9 =SUMPRODUCT(COUNTIF(INDIRECT(H1:H9&"!B1:B10"),10)) Biff |
#3
|
|||
|
|||
This was GREAT help. I had trouble using custom sheet names, so I just
renamed as sheet 1-10 and it all worked perfectly. Thanks! "Biff" wrote: Hi! If you use the default sheet names: Sheet1, Sheet2, Sheet3, etc .... Assume you want to Countif B1:B10 equals 10 on sheets 2:10 =SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT( "2:10")) &"!B1:B10"),10)) If you use custom sheet names .... List the sheet names in a range, say H1:H9 =SUMPRODUCT(COUNTIF(INDIRECT(H1:H9&"!B1:B10"),10)) Biff -----Original Message----- Sorry in advance for what is likely a very basic question, but I would appreciate help. I have created a workbook with multiple worksheets which all have the same format, but different data. For example, each row represents a person, and each column represents a time period. The row and column headers are the same in each worksheet, but what HAPPENED in the time periods varies. Iâ?Tm trying to create a formula that would look at the SAME range in each worksheet and count how many times a specific variable appears, returning simply the number of occurrences. It would seem to be a COUNTIF formula, but that function doesnâ?Tt seem to like 3-D references. Any ideas????? . |
#4
|
|||
|
|||
Hi!
I had trouble using custom sheet names, I'll bet that was because your sheet names had <spaces in=20 them. eg: Sales 04 Jan 05 Week 15 As opposed to sheet names like: Sales04 Jan05 Week15 If that's the case (sheet names with spaces), it's a=20 little more complicated! List your sheet names in the range H1:H9 and then name=20 that range: Select the range H1:H9. Click in the NAME box (that's the little "box" at the far=20 left of the formula bar that shows what cell you're in)=20 and type in a name, something like sheetnames. Then use this formula: =3DSUMPRODUCT(COUNTIF(INDIRECT("'"&sheetnames&"'! B1:B10"),10)) Biff -----Original Message----- This was GREAT help. I had trouble using custom sheet=20 names, so I just=20 renamed as sheet 1-10 and it all worked perfectly. =20 Thanks! "Biff" wrote: Hi! =20 If you use the default sheet names: Sheet1, Sheet2,=20 Sheet3, etc .... =20 Assume you want to Countif B1:B10 equals 10 on sheets=20 2:10 =20 =3DSUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIREC T ("2:10")) &"!B1:B10"),10)) =20 If you use custom sheet names .... =20 List the sheet names in a range, say H1:H9 =20 =3DSUMPRODUCT(COUNTIF(INDIRECT(H1:H9&"!B1:B10"),10 )) =20 Biff =20 -----Original Message----- Sorry in advance for what is likely a very basic=20 question, but I would=20 appreciate help. I have created a workbook with=20 multiple=20 worksheets which=20 all have the same format, but different data. For=20 example, each row=20 represents a person, and each column represents a time=20 period. The row and=20 column headers are the same in each worksheet, but=20 what=20 HAPPENED in the time=20 periods varies. I=C3=A2?Tm trying to create a formula=20 that=20 would look at the SAME=20 range in each worksheet and count how many times a=20 specific variable appears,=20 returning simply the number of occurrences. It would=20 seem to be a COUNTIF=20 formula, but that function doesn=C3=A2?Tt seem to like 3-D=20 references. Any ideas????? . =20 . |
#5
|
|||
|
|||
Hi!
I'm not sure what you mean by: would it be possible to link books together in a format like that? Can you be more specific? One thing you'll notice is the use of the Indirect function in the formula examples. You can link between WORKBOOKS using Indirect, however, Indirect REQUIRES that the other workbook be open otherwise you'll get an error. Biff -----Original Message----- Thank you Biff, that will help me in a project I will soon work on. To make it more difficult, would it be possible to link books together in a format like that? Such as Patient 1011.xls, and Patient 1012.xls Pull particular information from those books into one easy to read sheet? Thank you in advance. "Biff" wrote: Hi! If you use the default sheet names: Sheet1, Sheet2, Sheet3, etc .... Assume you want to Countif B1:B10 equals 10 on sheets 2:10 =SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT ("2:10")) &"!B1:B10"),10)) If you use custom sheet names .... List the sheet names in a range, say H1:H9 =SUMPRODUCT(COUNTIF(INDIRECT(H1:H9&"!B1:B10"),10)) Biff . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to protect and unprotect 30 worksheets in a file every month . | Excel Worksheet Functions | |||
Protect/unprotect all worksheets | Excel Worksheet Functions | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) | |||
Assigning Cells in worksheets to other data in other worksheets. | Excel Discussion (Misc queries) |