You could include a COUNTIF for each criteria cell:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2),COUNTIF(L4:P4, $F$2),
COUNTIF(L4:P4,$G$2)),ISNUMBER(SEARCH($D$2,D4)))

Secret Squirrel wrote:
Hi Debra,
Thank you, that worked perfectly. Now a followup question:

I currently have two ways of filtering my data, first from cell D2, and then
an optional one in E2. How would I re-write that formula to add a third, or
even forth filter? Say I wanted to use F4 & G4 as additional filters. These
two new filters would also use the same criteria range L4:P4.

Thanks!

"Debra Dalgleish" wrote:


If you're looking for the value in E2 in any cell in L4:P4, you could
use this formula:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2)),ISNUMBER(SEAR CH($D$2,D4)))

Secret Squirrel wrote:

I'm using an Advanced Filter to filter my data but I can't figure out how to
set up my criteria range. I currently have this as my criteria range:

=AND(OR($E$2="",L4=$E$2),ISNUMBER(SEARCH($D$2,D 4)))

Column L is one of the columns I want to filter my data by but I also want
to be able to filter other columns as well. Columns L thru P all have data
that I need to filter and I though just simply changing the above to this
would work:

=AND(OR($E$2="",L4:P4=$E$2),ISNUMBER(SEARCH($D$ 2,D4)))

But it didn't. How do I re-write this formula to be able to lookup up the
data in multiple columns?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html