count text through worksheets generates error

  • Thread starter Thread starter sybmathics
  • Start date Start date
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
 
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
 
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"))
 
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
 
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)
 
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.
 
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
 
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
 
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)
 
=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.
 
Back
Top