Examples of complex criteria

Use complex criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) when you create an advanced filter or when you use a database and list management function, such as DSUM.

 Important   

  • Because the equal sign is used to indicate a formula when you type text or a value in a cell, Microsoft Excel evaluates what you type; however, this may cause unexpected filter results. To indicate an equality comparison operator for either text or a value, type the criteria as a string expression in the appropriate cell in the criteria range:

=''=entry''

Where entry is the text or value you want to find. For example:

What you type in the cell What Excel evaluates and displays
="=Davolio" =Davolio
="=3000" =3000
The following sections provide examples of complex criteria


Multiple criteria in one column

Boolean logic:     (Salesperson = "Davolio" OR Salesperson = "Buchanan")

To find rows that meet multiple criteria for one column, type the criteria directly below each other in separate rows of the criteria range.

In the following data range (A6:C10), the criteria range (B1:B3) displays the rows that contain either "Davolio" or "Buchanan" in the Salesperson column (A8:C10).

  A B C
1 Type Salesperson Sales
2   =Davolio  
3   =Buchanan  
4      
5      
6 Type Salesperson Sales
7 Beverages Suyama $5122
8 Meat Davolio $450
9 produce Buchanan $6328
10 Produce Davolio $6544

Top of Page Top of Page

Multiple criteria in multiple columns where all criteria must be true

Boolean logic:     (Type = "Produce" AND Sales > 1000)

To find rows that meet multiple criteria in multiple columns, type all of the criteria in the same row of the criteria range.

In the following data range (A6:C10), the criteria range (A1:C2) displays all rows that contain "Produce" in the Type column and a value greater than $1,000 in the Sales column (A9:C10).

  A B C
1 Type Salesperson Sales
2 =Produce   >1000
3      
4      
5      
6 Type Salesperson Sales
7 Beverages Suyama $5122
8 Meat Davolio $450
9 produce Buchanan $6328
10 Produce Davolio $6544

Top of Page Top of Page

Multiple criteria in multiple columns where any criteria can be true

Boolean logic:     (Type = "Produce" OR Salesperson = "Davolio")

To find rows that meet multiple criteria in multiple columns, where any criteria can be true, type the criteria in different rows of the criteria range.

In the following data range (A6:C10), the criteria range (A1:B3) displays all rows that contain "Produce" in the Type column or "Davolio" in the Salesperson column (A8:C10).

  A B C
1 Type Salesperson Sales
2 =Produce    
3   =Davolio  
4      
5      
6 Type Salesperson Sales
7 Beverages Suyama $5122
8 Meat Davolio $450
9 produce Buchanan $6328
10 Produce Davolio $6544

Top of Page Top of Page

Multiple sets of criteria where each set includes criteria for multiple columns

Boolean logic:     ( (Salesperson = "Davolio" AND Sales >3000) OR (Salesperson = "Buchanan" AND Sales > 1500) )

To find rows that meet multiple sets of criteria, where each set includes criteria for multiple columns, type each set of criteria in separate rows.

In the following data range (A6:C10), the criteria range (B1:C3) displays the rows that contain both "Davolio" in the Salesperson column and a value greater than $3,000 in the Sales column, or displays the rows that contain "Buchanan" in the Salesperson and a value greater than $1,500 in the Sales column (A9:C10).

  A B C
1 Type Salesperson Sales
2   =Davolio >3000
3   =Buchanan >1500
4      
5      
6 Type Salesperson Sales
7 Beverages Suyama $5122
8 Meat Davolio $450
9 produce Buchanan $6328
10 Produce Davolio $6544

Top of Page Top of Page

Multiple sets of criteria where each set includes criteria for one column

Boolean logic:     ( (Sales > 6000 AND Sales < 6500 ) OR (Sales < 500) )

To find rows that meet multiple sets of criteria, where each set includes criteria for one column, include multiple columns with the same column heading.

In the following data range (A6:C10), the criteria range (C1:D3) displays rows that contain values between 5,000 and 8,000 and values less than 500 in the Sales column (A8:C10).

  A B C D
1 Type Salesperson Sales Sales
2     >6000 <6500
3     <500  
4        
5        
6 Type Salesperson Sales  
7 Beverages Suyama $5122  
8 Meat Davolio $450  
9 produce Buchanan $6328  
10 Produce Davolio $6544  

Top of Page Top of Page

Criteria to find text values that share some characters but not others

To find text values that share some characters but not others, do one or more of the following:

  • Type one or more characters without an equal sign (=) to find rows with a text value in a column that begin with those characters. For example, if you type the text Dav as a criterion, Excel finds "Davolio," "David," and "Davis."
  • Use a wildcard character.

ShowHow?

The following wildcard characters can be used as comparison criteria.

Use To find
? (question mark) Any single character
For example, sm?th finds "smith" and "smyth"
* (asterisk) Any number of characters
For example, *east finds "Northeast" and "Southeast"
~ (tilde) followed by ?, *, or ~ A question mark, asterisk, or tilde
For example, fy91~? finds "fy91?"

In the following data range (A6:C10), the criteria range (A1:B3) displays rows with "Me" as the first characters in the Type column or rows with the second character equal to "u" in the Salesperson column (A7:C9).

  A B C
1 Type Salesperson Sales
2 Me    
3   =?u*  
4      
5      
6 Type Salesperson Sales
7 Beverages Suyama $5122
8 Meat Davolio $450
9 produce Buchanan $6328
10 Produce Davolio $6544

Top of Page Top of Page

Criteria created as the result of a formula

You can use a calculated value that is the result of a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) as your criterion. Remember the following important points:

  • The formula must evaluate to TRUE or FALSE.
  • Because you are using a formula, enter the formula as you normally would, and do not type the expression in the following way:

=''=entry''

  • Do not use a column label for criteria labels; either keep the criteria labels blank or use a label that is not a column label in the range (in the examples below, Calculated Average and Exact Match).

If you use a column label in the formula instead of a relative cell reference or a range name, Excel displays an error value such as #NAME? or #VALUE! in the cell that contains the criterion. You can ignore this error because it does not affect how the range is filtered.

The following subsections provide specific examples of criteria created as the result of a formula.

Top of Page Top of Page

Filtering for values greater than the average of all values in the data range

In the following data range (A6:D10), the criteria range (D1:D2) displays rows that have a value in the Sales column greater than the average of all the Sales values (C7:C10). In the formula, "C7" refers to the filtered column (C) of the first row of the data range (7).

A B C D
1 Type Salesperson Sales Calculated Average
2       =C7>AVERAGE($C$7:$C$10)
3        
4        
5        
6 Type Salesperson Sales  
7 Beverages Suyama $5122  
8 Meat Davolio $450  
9 produce Buchanan $6328  
10 Produce Davolio $6544  

Top of Page Top of Page

Filtering for text by using a case-sensitive search

In the data range (A6:D10), the criteria range (D1:D2) displays rows that contain "Produce" in the Type column by using the EXACT function to perform a case-sensitive search (A10:C10). In the formula, "A7" refers to the filtered column (A) of the first row of the data range (7).

A B C D
1 Type Salesperson Sales Exact Match
2       =EXACT(A7, "Produce")
3        
4        
5        
6 Type Salesperson Sales  
7 Beverages Suyama $5122  
8 Meat Davolio $450  
9 produce Buchanan $6328  
10 Produce Davolio $6544  

Top of Page Top of Page

 
 
Applies to:
Excel 2003