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

#### mdershewitz

##### New Member
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!

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### mdershewitz

##### New Member
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
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

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

Replies
0
Views
54
Replies
9
Views
191
Replies
2
Views
81
Replies
4
Views
44
Replies
3
Views
240

1,129,573
Messages
5,637,149
Members
416,959
Latest member
Mohzein

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back