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

t33ge

New Member
Joined
Sep 25, 2014
Messages
12
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
 

Some videos you may like

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.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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
Joined
May 25, 2014
Messages
1,609

ADVERTISEMENT

=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,204
Messages
5,527,410
Members
409,760
Latest member
zeeshansyed

This Week's Hot Topics

Top