>2 text filter "or" criteria

[chopstxnrice]

Hey all,

I need help with something that sounds simple but I think is going to be complicated. Basically, I have data on a bunch of people in each row and I want to filter to see only patients that fit a certain criteria. For example, I want to filter to see only patients that, if the header is color, have either the words red, blue, or yellow in them. Some examples of other cells in the color column could be "red, blue, yellow, green" or "red, white" or "blue, yellow" or single colors. I want to be able to pull up a list of all patients that can fit a criteria of any of a number of given colors. I know I can use the custom filter but it seems to be limited to only 2 criteria.

I'm using this formula right now currently to search through the column of "colors" to search a list of keywords and come up with a total unique count. The previous function I was using was double counting cells that contained the words multiple times, but thanks to this board, it's working great now. Column A is the data I want to search through, and D2:T2 contains keywords I want to search for (17 in total). I guess ultimately, I not only want a count of how many total, but I want to be able to see the data that's being counted. Thanks in advance!

=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH(D2:T2,A:A))),{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17})>0))

 

[Armando Montes]

Check if this helps:
http://www.atlaspm.com/toms-tutoria...or-excel-advanced-filter-calculated-criteria/

 

[chopstxnrice]

Check if this helps:
http://www.atlaspm.com/toms-tutoria...or-excel-advanced-filter-calculated-criteria/

Thanks. I followed this exactly and used the formula =ISNUMBER(MATCH(A6,$N$1:$N$3,0)) to do an advanced filter in place to match any of a set of words, but I believe it is only doing exact matches of keywords. For instance, one criteria might be red, but in my data cells it might say "red, blue, green" and thus won't be displayed. How do I make it match any part of the cell, instead of exact match?

 

[chopstxnrice]

Thanks. I followed this exactly and used the formula =ISNUMBER(MATCH(A6,$N$1:$N$3,0)) to do an advanced filter in place to match any of a set of words, but I believe it is only doing exact matches of keywords. For instance, one criteria might be red, but in my data cells it might say "red, blue, green" and thus won't be displayed. How do I make it match any part of the cell, instead of exact match?

edit: i've tried encasing my criterion words in * but this just turns up empty results