The sheet names are "built" within the formula by the first Indirect
function. It creates an array of references like this:

COUNTIF(Sheet1!,H2)
COUNTIF(Sheet2!,H2)
COUNTIF(Sheet3!,H2)
COUNTIF(Sheet4!,H2)
..
COUNTIF(Sheet16!,H2)


Actually, that array of references would look like this:

COUNTIF(Sheet1!H2,"W")
COUNTIF(Sheet2!H2,"W")
COUNTIF(Sheet3!H2,"W")
etc
etc

Biff

"T. Valko" wrote in message
...
You don't need to change the sheet definition.

The sheet names are "built" within the formula by the first Indirect
function. It creates an array of references like this:

COUNTIF(Sheet1!,H2)
COUNTIF(Sheet2!,H2)
COUNTIF(Sheet3!,H2)
COUNTIF(Sheet4!,H2)
..
COUNTIF(Sheet16!,H2)

Then, Sumproduct adds up all the results.

If your sheet names are not the default names like, Sheet1, Sheet2,
Sheet3, etc., then we can tweak the formula. Post back if that's the case.

Biff

"T. Valko" wrote in message
...
Try these:

=SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT( "1:16"))&"!H2"),"W"))

=SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT( "1:16"))&"!H2"),"<"))

Biff

wrote in message
oups.com...
I have a number of identical sheets, and I'd like to be able to count
the occurances of a certain entry in a cell.

When I use the formula:

=COUNTIF('Sheet1:Sheet16'!H2, "W")

the cell displays ##### (error in value).

What's a formula I can use?

Also, I'd like to be able to do a similar thing, with non-blank values
(i.e. I'd like to count the number of non-blank cells across a number
of identical sheets).

Thanks in advance for your help.