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

mdershewitz

New Member
Joined
Jul 8, 2013
Messages
11
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!
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

mdershewitz

New Member
Joined
Jul 8, 2013
Messages
11
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,686
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

New Member
Joined
Jul 8, 2013
Messages
11
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,686
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

New Member
Joined
Jul 8, 2013
Messages
11
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
 

Forum statistics

Threads
1,089,283
Messages
5,407,363
Members
403,137
Latest member
ExcelGzh

This Week's Hot Topics

Top