Establishing Criteria
Excel needs criteria as a special feature for certain types of database functions. Criteria can be defined as the range of cells that includes the column label and a condition for that label. Criteria can be established to match a single field or multiple fields. It can be established for records to match multiple conditions (AND), match one of multiple conditions (OR), or match a range of conditions (BETWEEN). The criteria can be looking for an exact match or a match within a range of information. Using range names may make database functions easier to write.
Types of Conditions
To properly write criteria, it is important to understand how to format the condition for each criterion. There are three different formats: alphabetic conditions, numeric conditions, and date conditions. The following tables illustrate the conditions, the corresponding format, and a sample for each criteria.
Alphabetic Conditions
| Condition |
Format |
Sample |
| exact match |
="=text_string" |
="=john" |
| begins with |
text_string |
john |
greater than
or equal to |
>letter
>=letter |
>j
>=j |
less than
or equal to |
<letter
<=letter |
<j
<=j |
| between* |
>letter <letter |
>j <q |
*must be in separate cells within the same row
Numeric Conditions
| Condition |
Format |
Sample |
| exact match |
value |
15 |
| contains |
n/a |
n/a |
greater than
or equal to |
>value
>=value |
>15
>=15 |
less than
or equal to |
<value
<=value |
<15
<=15 |
| between* |
>value <value |
>15 <25 |
*must be in separate cells within the same row
Date Conditions
| Condition |
Format |
Sample |
| one date |
month/day/year |
4/1/1999 |
| contains |
n/a |
n/a |
date after
or equal to |
>month/day/year
>=month/day/year |
>4/1/1999
>=4/1/1999 |
date before
or equal to |
<month/day/year
<=month/day/year |
<4/1/1999
<=4/1/1999 |
| range of dates* |
>month/day/year<month/day/year |
>1/1/1999<12/31/1999 |
*must be in separate cells within the same row
Defining a Single Criterion
A single criterion defines a condition that when the database is searched, will return only one type of match for the particular field. The field name goes in the first cell; the condition for that field goes below the field name.
NOTE: In this example, only records where the pay period was equal to 15 would be evaluated for the database function.
Defining Multiple Criteria
Multiple criteria define conditions that when the database is searched, will return two or more matches. If both conditions must be met, the criteria need to be set up as AND. If a range of conditions must be met, the criteria need to be set up as BETWEEN. However, if only one of multiple conditions must be met, the criteria should be set up as OR.
AND … Match Two Conditions
For "AND" criteria, the fields are within the same row.
Format
|
Example
|
| Field name |
field name2 |
| condition |
condition |
|
| Pay Period |
Student |
| 15 |
Johnson |
|
NOTE: In this example, only records where the pay period is 15 and the student name contains Johnson would be evaluated for the database function.
BETWEEN ... Match Two Conditions
For "BETWEEN" criteria, the field is repeated in separate cells within the same row.
Format
|
Example
|
| Field name |
Field name |
| condition |
condition |
|
| Date |
Date |
| >3/31/2001 |
<6/30/2001 |
|
NOTE: In this example, only records between March 31, 2001 and June 30, 2001 would be evaluated for the database function.
OR ... Match Either of Two Conditions (Same Field)
For "OR" criteria with the same field, the field criteria are listed in a column under the field name.
Format
|
Example
|
| Field name |
| condition |
| condition |
|
|
NOTE: In this example, only records where the pay period is 15 or 16 would be evaluated for the database function.
OR … Match Either of Two Conditions (Different Fields)
For "OR" criteria with different fields, the conditions are listed under the appropriate field name but in separate rows so that they are not treated like "AND" conditions.
Format
|
Example
|
| Field name |
Field name 2 |
| condition |
|
| |
condition |
|
| Pay Period |
Student |
| 15 |
|
| |
Doe |
|
NOTES:
In this example, records where the pay period is 15 or the student name contains Doe would be evaluated for the database function.
