IF formula for differing text within two separate columns

  • Thread starter Thread starter Colin Hayes
  • Start date Start date
C

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
 
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
 
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
Click to expand...

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
 
You can change it to this:

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

Hope this helps.

Pete
 
You can change it to this:

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

Hope this helps.

Pete








- Show quoted text -
Click to expand...

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
 
Back
Top