| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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. |
|
#5
|
|||
|
|||
|
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. |
|
#6
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
