This browser does not support basic Web standards, preventing the display of our site's intended design. May we suggest that you upgrade your browser?

Microsoft Excel 2003/2004

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.

return to topTypes 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

return to topDefining 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.

Format

Example

Field name
condition
Pay Period
15

NOTE: In this example, only records where the pay period was equal to 15 would be evaluated for the database function.

return to topDefining 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
Pay Period
15
16

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.

Excellence. Our Measure. Our Motto. Our Goal.