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

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

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

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

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

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

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.

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

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.

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

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.