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

D

David

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

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)
 
D

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 said:
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)
Click to expand...
 
A

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)
Click to expand...

--

[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.
Click to expand...
 
D

David

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 said:
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


:
Click to expand...

--

[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.
Click to expand...
 
A

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.
Click to expand...
--

[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.
Click to expand...
Click to expand...

--

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