If, And Countif - Help with Multiple Conditions

Bigbudgie

New Member
Joined
Mar 10, 2016
Messages
2
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,796
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Are you now only looking for BN, rather than BN or TN?
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
how about?

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

with the 'another cell' in C3
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,636
Office Version
365
Platform
Windows
(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")

 

Watch MrExcel Video

Forum statistics

Threads
1,095,479
Messages
5,444,732
Members
405,298
Latest member
fxtrtr17

This Week's Hot Topics

Top