Using AND statement with IF(COUNTIF or IF(ISNUMBER(SEARCH

[t33ge]

Hi,

I have got a formula that determines if a cell contains a certain word/text and provides me with a desired text outcome if that is the case

I have found 2 ways of doing this:

=IF(ISNUMBER(SEARCH("abc",A9)),"result","")

=IF(COUNTIF(A9,"*abc*"),"result","")

However i cant seem to figure out how to do the same for two criteria. I.e if cell A9 contains somewhere within it 'abc' and a '?' then show 'result' if it doesn't contain these 2 words then show 'noresult'

Could anyone help?

Thanks

 

[NeonRedSharpie]

=IFERROR(IF(AND(FIND("abc",A9),FIND("?",A9)),"result",2),"noresult")

 

[barry houdini]

Try this version

=IF(ISNUMBER(SEARCH("abc",A9)+FIND("?",A9)),"result","noresult")

 

[Special-K99]

Try

=IF(ISNUMBER(SEARCH("abc",A9)+SEARCH("abc",A9)),"result","")

=IF(COUNTIF(A9,"*abc*")+COUNTIF(A9,"?")>0,"result","")

? in COUNTIF maybe interpreted as a wildcard
Might be better to use CHAR(63) instead of "?"

 

[WarPigl3t]

=IF(AND(ISNUMBER(SEARCH("abc",A9)), ISNUMBER(SEARCH("?", A9))),"result","")

=IF(AND(COUNTIF(A9, "*?*"), COUNTIF(A9,"*abc*")),"result","")

What is the question mark though? Because a quation mark is a wild card and you shouldn't use it like I just did in the formula. Replace the question mark with the second text you are looking for. If your formula needs the question mark for some reason, then I must have not understood your question.

 

[barry houdini]

? in COUNTIF maybe interpreted as a wildcard

...also SEARCH will interpret ? as a wildcard....but FIND won't. You can use "~" in front to indicate a literal "?", though

 

[t33ge]

Brilliant thanks guys all sorted