Array formulas don't work over multiple sheets. You can download Laurent
Longre's Morefunc
from here

http://xcell05.free.fr/english/

now what do you want to text, how many times "goat" occurs?

without UDFs you need to create a list of all sheet names you want to test
and put then in a range like H1:H101, then use something like

=SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H101&"'!A1")," goat"))

if it's part of a string

=SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H101&"'!A1")," *goat*"))

--
Regards,

Peo Sjoblom

(No private emails please)


"David" wrote in message
...
Greetings
I'm trying to find a compact formula.
On sheet1 cell A1, I want to test that cells A1 on sheets 2 through sheet
102 contain text: "goat".
Has anyone thought of a compact formula? (maybe it's an array formula)
TIA for your responses.
--
David