How do you get an advanced filter to work in Excel, with OR values?

[mdershewitz]

I have an advanced filter in Excel and I'm trying to set up functionality by which one criteria is equal to one of three values. I see some things online about using ORs with > and <, but not where (x=y) OR (x=z). How do I do this? thanks!

 

[AhoyNC]

See if this link helps.
At about the 4:20 mark he shows how to use OR with advanced filter.
https://www.youtube.com/watch?v=SU1qGbN6Rs8

 

[mdershewitz]

OK, this is very helpful - thanks!

One more scenario though. How do you turn this around in the same general construct to allow for the opposite? In other words, have (x<>y) AND (x<>z). As I have found out, you can't use the OR methodology in the video, because that will turn that into (x<>y) OR (x<>z) and therefore allow for the whole world. I therefore have to create a one-cell operator that has the negative AND.

Thanks!

 

[RoryA]

To use AND, you just repeat the column in the criteria range and put one criterion in one cell and the other in the next (repeat as many times as necessary)

 

[mdershewitz]

OK. Thanks for the help. I think I finally figured out what I'm trying to do, and none of the answers above 'quite' get you there. I'd love any help to get this Advanced Filter set up!

The logic for the advanced filter would be:

With the positive toggle, (CountryCode = 'France') OR (CountryCode = 'Germany')

With the negative toggle, (CountryCode <> 'France') AND (CountryCode <> 'Germany'). If you put an OR on this line then Germany and France both come up.

I'm having trouble using multiple rows and columns on the filter criteria of Excel to do what I am aiming to do. Thanks!

 

[RoryA]

You haven't really told us what the problem is or what your layout is. Essentially you'd have two columns in the criteria range titled CountryCode and enter <>France under one and <> Germany under the other. That will AND the two.

Generally speaking if you are trying to combine this with other criteria, it may well be easier to use a formula criteria range instead.

 

[mdershewitz]

Thanks! I'd love to use a formula criteria range...but I haven't read about that yet! Do you have a link you could shoot me on formula criteria ranges?

Thanks

 

[RoryA]

There's an explanation here that may be useful: Advanced filters. Who knew? | Excel Matters

Or you could start here: http://www.contextures.com/xladvfilter01.html