IF formula for differing text within two separate columns

[Colin Hayes]

Hi

I need some help with an IF formula.

I'm trying to say that IF A1 contains "A" anywhere within it AND IF B1
contains "B" anywhere within it then put 'YES' , otherwise put 'NO'.

Can someone help?



Best Wishes

 

[Pete_UK]

Try this:

=IF(AND(COUNTIF(A1,"*A*"),COUNTIF(B1,"*B*")),"YES","NO")

The asterisk is the wildcard character, meaning "anything" in this
case.

Hope this helps.

Pete

 

[Colin Hayes]

Try this:

=IF(AND(COUNTIF(A1,"*A*"),COUNTIF(B1,"*B*")),"YES","NO")

The asterisk is the wildcard character, meaning "anything" in this
case.

Hope this helps.

Pete

Hi Pete

Yes that's perfect. Thank you.

As a variant , could this be amended to say IF A1 contains "A" anywhere
within it AND IF B1 does NOT contain "B" anywhere within it then put
'YES' , otherwise put 'NO'?

Grateful for any advice.

Thanks

 

[Pete_UK]

You can change it to this:

=IF(AND(COUNTIF(A1,"*A*"),COUNTIF(B1,"*B*")=0),"YES","NO")

Hope this helps.

Pete

 

[Don Guillett Excel MVP]

You can change it to this:

=IF(AND(COUNTIF(A1,"*A*"),COUNTIF(B1,"*B*")=0),"YES","NO")

Hope this helps.

Pete








- Show quoted text -

Or instead of a lot of formulas, use a macro using INSTR
Option Compare Text
Sub YesNO()
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
For i = 1 To lr
If InStr(Cells(i, 1), "a") > 0 And _
InStr(Cells(i, 2), "b") = 0 Then
'MsgBox "Yes"
Cells(i, "c") = "Yes"
End If
Next i
End Sub