Version 3.9 allows to make a workbook include morefunc. Look at the
option Tools|Morefunc after installation.
David wrote:
Aladin,
Thanks for your time
i'll try COUNTIF.3D
I wont have a network to test it on 'til Monday, I'll be using it in a
shared workbook on a network.
Will COUNTIF.3D work on any network pc without having to download something
to each?
TIA
--
David
"Aladin Akyurek" wrote:
Why not invoke Morefunc's COUNTIF.3D...
=COUNTIF.3D(Sheet2:Sheet102!A1,"*goat*")
David wrote:
Peo,
Thanks for your response. It's really useful to know that you can use wild
cards with countif (seems to have been missed on excel help)
... I think I'll go for a UDF rather than a long list
Much appreciated
--
David
"Peo Sjoblom" wrote:
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
--
[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
--
[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.