If, And Countif - Help with Multiple Conditions

[Bigbudgie]

Hello,
I was hoping someone could please help with a multiple conditions formula?

I am trying to use two criteria to produce a result, I was initially using "IF and OR" but because I was using postcodes and was trying to match on the first two characters am now using SUM(COUNTIF {** **} as I understand I cannot use wild cards in this situation.

The formula I am currently using is:
=IF(SUM(COUNTIF(C2,{"*BN*", "*TN*"})), "Yes"","No")

This matches if the cell contains either of the above prefixes in the cell (part of the postcode),

Works great but I now want to add a second criteria or to add a second condition from another cell " If another cell contains for example "Oranges", so the new conditions would need to be "oranges" and "BN" to show "Yes", this is where I am stuck and am looking to hopefully get some help.


Many thanks

 

[RoryA]

Are you now only looking for BN, rather than BN or TN?

 

[AlanY]

how about?

=if(AND(OR(LEFT(C2,2)="BN",LEFT(C2,2)="TN"),C3="Oranges"),"Yes","No")

with the 'another cell' in C3

 

[Yongle]

(Untested)

How about this method to add second condition
=IF(SUM(COUNTIF(C2,{"*BN*", "*TN*"})),IF(D2="Oranges", "Yes","No"))
- amend second IF condition to meet required criteria
OR
=IF(AND(SUM(COUNTIF(C2,{"*BN*", "*TN*"})),D2="Oranges"),"Yes","No")