Using “And”, “Or” and Multiple Criteria’s

1.1 And Criteria

Following picture contains range I1:N2 wherein we have couple of criteria:

1) State should be Colorado
2) Manager should be Susan
 

 
Advance filter will retrieve the data that matches both the conditions; we need to follow the below steps:

• Use ALT + A + Q to launch Advanced Filter dialog box

• The List range box will contain range A1:F273; the whole data table
• In Criteria range,select the criteria range as I1:N2
• Select Copy to another location & enter cell I5 in Copy to; wherein advance filtered data will be retrieved

• Click on OK button will give us the filtered data.Refer below snapshot

 

 
In this way, we can use ‘AND’ condition to get the data that matches both the conditions.

We can have multiple conditions to get the output that matches all the conditions.

1.2 OR Criteria

Following is the picture of ‘OR’ criteria

Advance filter will look for any one condition:

1)   Manager should be Susan & Process should be XYZ

2)   Manager should be Ronald & Process should be PMP
 

 

• If any condition is met then the data will be extracted
• Use ALT + A + Q to launch Advanced Filter dialog box

• The full data will be listed in List range
• In Criteria range, select the criteria range as I1:N3
• Select Copy to another location & enter cell I5 in Copy to; wherein advance filtered data will be retrieved.

 

• Click on OK button will give us the filtered data.Refer below snapshot

 

 
In this way, we can use ‘OR’ condition to get the data that matches any one condition & produces result accordingly.
 
1.3   Multiple Criteria

Following is the picture of Multiple Criteria

Advance filter will check for data that matches all the entries in a row; if all the conditions will meet in a row then the data will be retrieved using advanced filter.
 

 
We need to follow the below steps:

• Use ALT + A + Q to launch Advanced Filter dialog box

• Enter the data range in List range field
• In Criteria range, select the criteria range as I1:N3
• Select Copy to another location & enter cell I5 in Copy to; wherein advance filtered data will be retrieved.

• Click on OK button will give us the filtered data.Refer below snapshot

 

 
In this way, we can use multiple conditions to get the data that matches any one condition & gets us the result.
 
ADVANCE FILTER INDEX:

Introduction to Advance Filter
1. Using “And”, “Or” and Multiple Criteria
2. How to Filter Unique Records?
3. How to Use Wildcards?
4. Extract records after specific date criteria
5. Extract items between 2 Date’s criteria
6. How to extract data with specific text?
7. How to extract data to another worksheet using VBA?
 

Download E-Book