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
 
img8
 
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

 
img9
 

  • 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

 
img11
 

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

 
img10
 
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
 
img12
 

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

 
img13
 

  • 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.
  •  

img14
 

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

 
img15
 
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.
 
img16
 
We need to follow the below steps:

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

 
img17
 

  • 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.

 
img18
 

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

 
img19
 
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
 
 



Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>