Counting Occurances across a range of sheets

[grahambae]

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.

 

[T. Valko]

Try these:

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

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

Biff

 

[grahambae]

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.

 

[Domenic]

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

 

[T. Valko]

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]

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

 

[grahambae]

That's awesome guys, it works perfectly now.

Thanks for all your help!

GB.

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

 

[frankjh19701]

You're welcome. Thanks for the feedback!

Biff

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.