Count text across many sheets (Not VBA)

=countif(MySheet1:MySheet132!M8,"Query")

To achieve this, list all your worksheets name in the range Z1:Z132 (MySheet1,MySheet2,MySheet3.........MySheet132) on Summary Sheet. Then try this......

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I didn't test it before posting.

Now I tested it, and unfortunaly i got an failure.

I don't have an solution on this one.


I would solve it differant.

Add all data on 1 sheet (e.g. with an macro).

After that use a pivot table to analyze the data.

Originally Posted by Phil Payne

Thanks oeldere,

I tried this but unfortunately this brings up #VALUE! error!

As oeldere should know since COUNTIF is not capable of operating on 3D ranges as such.

You would need to do it as follows:

First enter all of your sheet names (precisely as they appear on the tabs) into a vertical range somewhere in your master sheet, e.g. E1:E132.

A quick way to do this (assuming your sheet names follow the pattern "MySheet" followed by the integers from 1 to 132) would be to enter "MySheet" into F1, copy down to F132 and then enter this formula into E1:

=F1&ROWS($1:1)

and copy down to E132.

Your formula would then be:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&E1:E132&"'!M8"),"Query"))

If you don't like the idea of having your sheet names in the actual worksheet, there is an alternative in which we could store them directly into Name Manager. Let me know if that would be preferable to you.

Regards

Actually I think your simple solution is perfectly good, and I would doubt very much that it will add much in terms of cost in resource. Nor is it something I would call particularly "complex"!

Regards