AND and OR for Advanced Filter range on large number of ORs

Hi and thanks in advance,

I'm using Excel 2010 and trying to build a userform that uses AND and OR on a number of data columns and then using this criteria range on an Advanced filter. I have one sheet with the data and the criteria range off to the top right of the data and then on another sheet, I output the filtered data. This works well for a fairly basic set of ANDing or ORing criteria, where there are just two rows of critieria (so just the one OR). I understand that you AND everything on a single criteria row and that ORs are applied between the rows but wondering how that is represented on the sheet in the case of ORing a larger number of values. How would you output the criteria to the sheet for the Advanced filter?

For instance,

Suppose I had criteria that I wanted to OR for each of 3 or more columns and then AND each set of ORed criteria ...

So if you were writing a formula, the logic would be something like:

(Below I've just included 4 criteria for each column as an example, but there could be more or less criteria for each column and more or less columns overall)

AND(OR(Col1="Dog",Col1="Cat", Col1="Rat", Col1="Bat", ...),OR(Col2="A", Col2="B",Col2="C",Col2="D",...),OR(Col3="1",Col3="2",Col3="3",Col3="4",...))

Thanks
deutz

Re: AND and OR for Advanced Filter range on large number of ORs

After mucking about with this for a while it appears that the easiest solution was simply to build a formula with COUNTIFS and place it in a cell on the data sheet and run the advanced filter off that formula rather than try to figure out what rows and columns to place each piece of the criteria into.

Regards
deutz

Re: AND and OR for Advanced Filter range on large number of ORs

Thanks rory,

That's what I figured. Sometimes you have the solution right under your nose.

Regards
deutz