Help with counting across worksheets

  • Thread starter Thread starter Biff
  • Start date Start date
B

Biff

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
 
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.
 
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!
 
Hi!
I had trouble using custom sheet names,
Click to expand...

I'll bet that was because your sheet names had <spaces> in
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
little more complicated!

List your sheet names in the range H1:H9 and then name
that range:

Select the range H1:H9.

Click in the NAME box (that's the little "box" at the far
left of the formula bar that shows what cell you're in)
and type in a name, something like sheetnames.

Then use this formula:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheetnames&"'!
B1:B10"),10))

Biff
 
Hi!

I'm not sure what you mean by:
would it be possible to link books together in a format
like that?
Click to expand...

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
 
Back
Top