Counting Occurances across a range of sheets
 

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.

Counting Occurances across a range of sheets
 

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.

Counting Occurances across a range of sheets
 

Hmmm, I tried those formulae, but I get an invalid cell reference
error after substituting in the respective sheet names.

If I use

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

the cell displays the correct number of W's, but obviously I want to
count across a number of sheets.

Counting Occurances across a range of sheets
 

If your sheets are named Sheet1 through Sheet16, as per your original
post, there's no need to change Biff's formula.

In article .com,
wrote:

Hmmm, I tried those formulae, but I get an invalid cell reference
error after substituting in the respective sheet names.

If I use

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

the cell displays the correct number of W's, but obviously I want to
count across a number of sheets.

Counting Occurances across a range of sheets
 

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.

Counting Occurances across a range of sheets
 

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.

Counting Occurances across a range of sheets
 

That's awesome guys, it works perfectly now.

Thanks for all your help!

GB.

Counting Occurances across a range of sheets
 

You're welcome. Thanks for the feedback!

Biff

wrote in message
ps.com...
That's awesome guys, it works perfectly now.

Thanks for all your help!

GB.

What if someone wanted to add the values that match the criteria found? I.E. for every time X was found in column A and Y was beside it in Column B, then add the value (Also adjacent to them in the same row) in Column D. Is this possible? And not counting the occurences, just the value in Column D all the way down the sheet.