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(INDIRECT("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?????
>.
>

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
>

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

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(INDIRECT
("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
>.
>

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