Setting up SharePoint list or library filtered views with multiple conditions can sometimes be a little tricky as the order that you put the conditions determine whether the view will filter successfully or not. Most of the problems occur when creating Filtered views that contain both AND and OR conditions. The simple rule of thumb I use when contains both is to put the OR conditions before the AND conditions.
So for example if I wanted to create an Overdue Open Tasks view, if I put the AND condition before the OR condition, the ‘Test Issue 2’ item appears in my view even though it doesn’t meet the Due Date is less than today condition:
But if I put the OR condition before the AND condition, now the filtered view works correctly and only returns Pending or In Progress items where the Due Date is less than Today:
To show a little more complex example, I’m going to create a My Overdue Open Tasks view which is basically the same as the view above but now additionally only returns items Assigned to Me. Again if I put an AND condition before an OR condition, the view returns an item that shouldn’t be there:
But if I put the OR before the AND’s, now the filter works correctly:
Sometimes an easier way to create filtered views with multiple conditions, is to create a calculated field to combine conditions together. For example you could use a calculated field to mark all ‘In Progress’ OR ‘ Pending’ items as ‘Active’ in the calculated column. Not only does this simplify the conditions for creating views, but if you ever add a third condition like ‘Waiting on Someone’ that you want to show up in all your views, you can just edit the one calculated column formula instead of having to update all your views. To learn how this is done see the Using a Calculated field to simplify Filtered Views tutorial.
If you would like to understand the logic behind why the SharePoint views filter like they do, here’s a good in depth post on the Nothing but SharePoint blog: