Array Formula Question

[jamilm]

Gurus,

based one what of the array formula, i learned from Aladin. i had a criteria as part of my mega formula which was --ISNUMBER(FIND("America",CountryRange)))
Now, i want to change this to seach also for text "Kingdom" so i changed it to --ISNUMBER(FIND({"America","Kingdom"},CountryRange))) but it did not work. gives me value error.


am i missing something in the formula?

any help is appreciated.

 

[Chris Macro]

What about: =IsNumber(Match({"America","Kingdom"},CountryRange,0))

 

[Jonmo1]

It works for me, can you post the entire formula that it's being used in?

 

[jamilm]

What about: =IsNumber(Match({"America","Kingdom"},CountryRange,0))

match does not help on this case. becuase part of the string in the cell is "America" or "Kingdom" the cell value is like UnitedStatesofAmerica or UnitedKingdom

 

[Chris Macro]

try "*America*","*Kingdom*"

 

[Jonmo1]

Without seeing the 'entire' formula, this is only a stab in the dark...

But try

--ISNUMBER(FIND({"America";"Kingdom"},CountryRange)))

 

[jamilm]

Hi Jonmo1

thanks for looking into this. appreciated.

the complete formula was =SUMPRODUCT(AMOUNT,--ISNUMBER(MATCH(FISCAL_YEAR,{2012,2011,2010,2009,2008,2007,2006,2005,2004},0)),1-ISNUMBER(MATCH(ACCOUNTING_PERIOD,{0,221,222},0)),1-(FUND="BBB"),--ISNUMBER(MATCH(LEFT(ACCOUNT&"#####",5),{"50505"},0)),1-ISNUMBER(FIND("America",CountryRange)))

the last criteria to exclude/ignore the AMOUNT if "America" text was found as part of the cell in the CountryRange . i wanted to put one more condition that if either "America" or "Kingdom" was found then exclude/ignore the amount.

i managed to get it work with adding one more criteria at the end like this the one in red =SUMPRODUCT(AMOUNT,--ISNUMBER(MATCH(FISCAL_YEAR,{2012,2011,2010,2009,2008,2007,2006,2005,2004},0)),1-ISNUMBER(MATCH(ACCOUNTING_PERIOD,{0,221,222},0)),1-(FUND="BBB"),--ISNUMBER(MATCH(LEFT(ACCOUNT&"#####",5),{"50505"},0)),1-ISNUMBER(FIND("America",CountryRange)),1-ISNUMBER(FIND("Kingdom",CountryRange)))

and this one works for me. but i was wondering how to incorporate it like in simplified version 1-ISNUMBER(FIND({"America","Kingdom"},CountryRange))) perhaps this means it triggers like AND not OR.

any idea?

It works for me, can you post the entire formula that it's being used in?