# 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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,305
Messages
5,836,539
Members
430,436
Latest member
Emilycr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back