Advanced Filter: Multiple <>

[Texas Longhorn]

I'm using advanced filter on a list, and I'm trying to use multiple "does not equals". Here's what I've got:

Book4
	A	B	C	D	E	F
1	Int'l	Reg	Country	Dir	Mgr	Rep
2				<>NA
3				<>DL
4
5
6
7	Dir	Mgr	Rep
8	NXB	SLGR	GM
9	NXB	NBC	GAL
10	NXB	SLG	CLW
11	DL	DL	MH
12	DL	DL	GK
13	PGO	KAC	SBA
14	NXB	SLG	ERM
15	NXB	SLG	JPB
16	PGO	KAC	BMM
17	DL	PM	JR
18	MJM	MLW	DXS
19	PMP	BLS	CMS
20	MJM	JMM	JMK
21	PMP	BLS	BPB
22	DL	DL	AM
23	NA	NA	DAS
24	PMP	PAK	HRM
25	PMP	DWB	DTT
26	PMP	DWB	JPB
Sheet2

I'm not sure why this is failing to filter out DL and NA. When I enter just one criterion (e.g.<>DL), the filter works, but when I attempt multiple<>s, it does not.

What am I doing wrong?

Many thanks,

Bill

 

[bjwillingham]

If the condition is an 'and' it must be contained in the same row.

Multiple rows are 'or's.

Try two 'DIR' columns with each having one of the criteria

 

[Texas Longhorn]

Brad - thanks. Your suggestion to use multiple DIR columns worked. I didn't realize I could do that - I thought my criteria headings had to match my source data headings. Definitely good to learn otherwise.

Thanks again,

Bill

 

[Ekim]

Bill,

If your criterion in row 2 is a formula, then row 1 must be blank. The following works too.

D2:
=AND(A8<>"NA",A8<>"DL")

Book1
	A	B	C	D	E	F	G	H
1	Int'l	Reg	Country		Dir	Mgr	Rep
2				TRUE
3
4
5
6						OutputRange:
7	Dir	Mgr	Rep			Dir	Mgr	Rep
8	NXB	SLGR	GM			NXB	SLGR	GM
9	NXB	NBC	GAL			NXB	NBC	GAL
10	NXB	SLG	CLW			NXB	SLG	CLW
11	DL	DL	MH			PGO	KAC	SBA
12	DL	DL	GK			NXB	SLG	ERM
13	PGO	KAC	SBA			NXB	SLG	JPB
14	NXB	SLG	ERM			PGO	KAC	BMM
15	NXB	SLG	JPB			MJM	MLW	DXS
16	PGO	KAC	BMM			PMP	BLS	CMS
17	DL	PM	JR			MJM	JMM	JMK
18	MJM	MLW	DXS			PMP	BLS	BPB
19	PMP	BLS	CMS			PMP	PAK	HRM
20	MJM	JMM	JMK			PMP	DWB	DTT
21	PMP	BLS	BPB			PMP	DWB	JPB
22	DL	DL	AM
23	NA	NA	DAS
24	PMP	PAK	HRM
25	PMP	DWB	DTT
26	PMP	DWB	JPB
Sheet2

Regards,

Mike