IF(<criterion across sheets>,1,0)?

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>

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" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>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.

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" <[email protected]> wrote in message
> >>news:[email protected]...
> >>
> >>>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.
>

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" <[email protected]> wrote in message
>>>>news:[email protected]...
>>>>
>>>>
>>>>>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.