count text through worksheets generates error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sybmathics - 06 Oct 2007 11:37 GMT

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

Reply to this Message

Ken Johnson - 06 Oct 2007 12:13 GMT

On Oct 6, 8:37 pm, sybmathics <startingafterthissybol...@xs4all.nl>
wrote:

> Hi all,
>
[quoted text clipped - 20 lines]
>
> Sybolt

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

Reply to this Message

sybmathics - 06 Oct 2007 12:46 GMT

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.

Reply to this Message

Peo Sjoblom - 06 Oct 2007 12:33 GMT

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"))

Signature

Regards,

Peo Sjoblom

> Hi all,
>
[quoted text clipped - 21 lines]
>
> Sybolt

Reply to this Message

Bob Phillips - 06 Oct 2007 12:43 GMT

Slight simplification of Peo's formula

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

Signature

---
HTH

Bob

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

> It's because Excel is quite limited when it comes to 3D functionality, you
> can use this
[quoted text clipped - 26 lines]
>>
>> Sybolt

Reply to this Message

Peo Sjoblom - 06 Oct 2007 13:01 GMT

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

Signature

Regards,

Peo Sjoblom

> Slight simplification of Peo's formula
>
[quoted text clipped - 30 lines]
>>>
>>> Sybolt

Reply to this Message

Bob Phillips - 06 Oct 2007 16:14 GMT

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 :-)

Signature

---
HTH

Bob

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

>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
[quoted text clipped - 34 lines]
>>>>
>>>> Sybolt

Reply to this Message

sybmathics - 06 Oct 2007 13:02 GMT

Bob Phillips schreef:

> Slight simplification of Peo's formula
>
> =SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:5"))&"'!B2"),"Yes"))

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

Reply to this Message

T. Valko - 06 Oct 2007 21:34 GMT

> =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.

Signature

Biff
Microsoft Excel MVP

> Slight simplification of Peo's formula
>
[quoted text clipped - 30 lines]
>>>
>>> Sybolt

Reply to this Message

Ken Johnson - 06 Oct 2007 12:35 GMT

On Oct 6, 8:37 pm, sybmathics <startingafterthissybol...@xs4all.nl>
wrote:

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

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

Reply to this Message