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

#### t33ge

##### New Member
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

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### NeonRedSharpie

##### Well-known Member
Code:
``=IFERROR(IF(AND(FIND("abc",A9),FIND("?",A9)),"result",2),"noresult")``

#### barry houdini

##### MrExcel MVP
Try this version

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

#### Special-K99

##### Well-known Member
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

##### Well-known Member

=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

##### MrExcel MVP
? 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

##### New Member
Brilliant thanks guys all sorted

Replies
4
Views
120
Replies
6
Views
72
Replies
2
Views
68
Replies
4
Views
1K
Replies
6
Views
97