count text through worksheets generates error

S

sybmathics

Hi all,

In a workbook with a lot of worksheets i want to count the number of
times a specific text value occurs in a cell.

All sheets have the same layout.

So, for instance, when i want to count the number of times the value
"yes" occurs in cell B2 through sheets 1 to 5 and i use the function
=countif(sheet1:sheet5!b2,"yes") excel generates an error message (#value!).

I don't understand why, because if i would use the function in 1 sheet
only I get the required result.

I suspect that some functions cannot work on grouped sheets.
Is there a list somewhere that explains which functions you either can
or cannot use in grouped sheets?

Has anybody a suggestion which function i could use to count a specified
text value through worksheets?

TIA.

Sybolt
 
K

Ken Johnson

Hi all,

In a workbook with a lot of worksheets i want to count the number of
times a specific text value occurs in a cell.

All sheets have the same layout.

So, for instance, when i want to count the number of times the value
"yes" occurs in cell B2 through sheets 1 to 5 and i use the function
=countif(sheet1:sheet5!b2,"yes") excel generates an error message (#value!).

I don't understand why, because if i would use the function in 1 sheet
only I get the required result.

I suspect that some functions cannot work on grouped sheets.
Is there a list somewhere that explains which functions you either can
or cannot use in grouped sheets?

Has anybody a suggestion which function i could use to count a specified
text value through worksheets?

TIA.

Sybolt
Click to expand...

You're right, COUNTIF is not one of the functions that can be used
with a 3-d reference

Look in Help under the heading...

Refer to the same cell or range on multiple sheets

Ken Johnson
 
P

Peo Sjoblom

It's because Excel is quite limited when it comes to 3D functionality, you
can use this

=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5"}&"'!B2"),"Yes"))
 
K

Ken Johnson

Has anybody a suggestion which function i could use to count a specified
text value through worksheets?
Click to expand...


If some other cell, say B3 on each sheet has the formula...

If(B2="yes",1,0)

then you could use

=SUM(Sheet1:Sheet5!B3)

Ken Johnson
 
B

Bob Phillips

Slight simplification of Peo's formula

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:5"))&"'!B2"),"Yes"))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
S

sybmathics

Thanks, Ken, for the quick reply.

I must say that I'm a bit disappointed.

Most functions available for 3d references are from the statistical
category.
So why not the countif function, one wonders.
 
P

Peo Sjoblom

I actually used it the way I did because many are the times when OPs post
back and say his/her sheets have in fact unique names without any patterns
etc


--

Regards,

Peo Sjoblom
 
S

sybmathics

Bob Phillips schreef:
Slight simplification of Peo's formula

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:5"))&"'!B2"),"Yes"))
Click to expand...

Thanks Peo en Bob for the suggestions.
I did think of a workaround myself, but i just wondered why the countif
didn't work, because you would expect this 3d functionality.

Any hope in next Excel versions maybe...?


cheers,

Sybolt
 
B

Bob Phillips

I prefer a lookup table in those situations Peo. No matter what technique
you use, if the OP wants an explanation, it's hard to explain :)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
T

T. Valko

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:5"))&"'!B2"),"Yes"))

Yet another way to write that:

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&{1,2,3,4,5}&"'!B2"),"Yes"))

Of course, if there were 100 sheets involved you'd want to use Bob's method.
 
Top