10 Replies Latest reply on Sep 6, 2013 8:49 AM by Katie Minks

# filtering: 2 conditions are TRUE, one is FALSE

I have the following condition:

Look only at the customers that visit all three shops. For this purpose I use the following formula:

MAX(IIF([Shop 1]=1,1,0))+

MAX(IIF([Shop 2]=1,1,0))+

MAX(IIF([Shop 3]=1,1,0))=3

Now I need to look only at the customers that are visiting shop 1 and 2 and NOT 3. Could you please help me with the formula?

• ###### 1. Re: filtering: 2 conditions are TRUE, one is FALSE

MAX(IIF([Shop 1]=1,1,0))+

MAX(IIF([Shop 2]=1,1,0))+

MAX(IIF([Shop 3]<>1,1,0))=3

1 of 1 people found this helpful
• ###### 2. Re: filtering: 2 conditions are TRUE, one is FALSE

I tried your formula and it indeed showed all the visitors that came to both Shop 1 and Shop 2. However, for some reason, it has also taken into consideration some of the visitors that came to the Shop 3.

In my case I only need to calculate the amount of visitors that came to the shop 1 and 2, and not 3.  Please see attached the file with the screenshot (I put a red frame around only the results with the visitors that I am interested in).

Do you know how to make it possible? Thank you!

• ###### 3. Re: filtering: 2 conditions are TRUE, one is FALSE

It is not clear how your data is laid out. Or how your viz is generated. Best solution is to post sample data and/or sample workbook.

• ###### 4. Re: filtering: 2 conditions are TRUE, one is FALSE

Please see a file attached with a sample workbook. I would appreciate it a lot if you could help me. Thanks!

• ###### 5. Re: filtering: 2 conditions are TRUE, one is FALSE

Julia please post a packaged workbook (twbx). The one you posted (twb) doesn't include any data.

--Shawn

• ###### 6. Re: filtering: 2 conditions are TRUE, one is FALSE

Sorry, Shawn!

• ###### 7. Re: filtering: 2 conditions are TRUE, one is FALSE

If this one cannot be opened as well please see excel version.

• ###### 8. Re: filtering: 2 conditions are TRUE, one is FALSE

Julia I'm a bit confused. I don't see why you're using the MAX() function IF statement combo to filter your data. I don't see what it gets you. Take a look at the 3 different tabs and see if this gets you what you want. If it doesn't add a worksheet showing me how you're implementing your original calculation.

--Shawn

• ###### 9. Re: filtering: 2 conditions are TRUE, one is FALSE

Thank you very much Shawn! This is exactly the result I wanted to get!

• ###### 10. Re: filtering: 2 conditions are TRUE, one is FALSE

I tried this solution for this sort of problem and it said that I had a syntax error and that I may be missing an operator.  Do you have any idea what could be going on with that since I tried your solution:

MAX(IIF([Shop 1]=1,1,0))+

MAX(IIF([Shop 2]=1,1,0))+

MAX(IIF([Shop 3]<>1,1,0))=3