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

[Guest]

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.

 

[Peo Sjoblom]

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)

 

[Guest]

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

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)

 

[Aladin Akyurek]

Why not invoke Morefunc's COUNTIF.3D...

=COUNTIF.3D(Sheet2:Sheet102!A1,"*goat*")

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


:

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)

--

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

 

[Guest]

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

Why not invoke Morefunc's COUNTIF.3D...

=COUNTIF.3D(Sheet2:Sheet102!A1,"*goat*")

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


:

--

[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 Akyurek]

Version 3.9 allows to make a workbook include morefunc. Look at the
option Tools|Morefunc after installation.

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

:

Why not invoke Morefunc's COUNTIF.3D...

=COUNTIF.3D(Sheet2:Sheet102!A1,"*goat*")

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


:



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)




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.

--

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