Leading the way in Microsoft Office Development

This explains how the criteria that is passed to a database function can be defined.

There a simple examples of database functions and they all use the following data table:

The method used to specify your criteria is the same method used when using the Advanced AutoFilter.

For the purpose of our examples, this range of cells "B2:E17" has the named range "Database_Range".

If you select a single cell in a list of data then the range used is defined as the region separated by a blank row and column.

If you select a range of cells in a list of data then this range is the ranges used.

1 Column - 1 Criteria

You can filter the data using a single column to express a single condition.

This example will include only rows that contain "John" in the "Name" column.

There are 3 rows (or cells) that meet this criteria: B3, B7 & B15

1 Column - 3 Criteria

You can filter the data using a single column to express multiple conditions.

This example will include all the rows that contain either "John", "James" or "Nick" in the "Name" column.

There are 6 rows (or cells) that meet this criteria: B3, B6, B7, B8, B14 & B15

2 Columns - 1 Criteria

You can filter the data using multiple columns to express a single condition.

This example will include all the rows that start with a "J" in the "Name" column and that have a value <19,000 in the "Salary" column.

There are 3 rows (or cells) that meet this criteria: B3, B10 & B15

2 Columns - 2 Criteria

You can filter the data using multiple columns to express multiple conditions.

This example will include all the rows that start with a "J" in the "Name" column OR have a value <19,000 in the "Salary" column.

There are 8 rows (or cells) that meet this criteria: B3, B5, B6, B7, B10, B12, B13 & B15

2 Columns - 3 Criteria

You can filter the data using multiple columns to express multiple conditions.

This example will include all the rows that start with a letter "M" in the "Name" column and have a value >24,000 in the "Salary" column OR start with a "J" in the "Name" column OR have a value <19,000 in the "Salary" column.

There are 9 rows (or cells) that meet this criteria: B3, B5, B6, B7, B9, B10, B12, B13 & B15

It is important to note that there are actually 10 rows that meet this criteria, but since cell C11 contains text it is not included in the DCOUNT() function.

3 Columns - 3 Criteria

You can filter the data using multiple columns to express multiple conditions.

This example will include all the rows that start with a letter "J" in the "Name" column and have a value either >19,000 or <22,000 in the "Salary" column OR have a value <18,000 in the "Salary" column.

There are 5 rows (or cells) that meet this criteria: B5, B6, B7, B12 & B13

Using Formulas

It is possible to also use cells that contain formulas as part of your criteria.

When using a formula you must use a column label that does not exist in your table or leave it blank. The appropriate column is referenced by the cell range in the formula.

Your formulas must use relative references to refer to the corresponding column label and all other references must be absolute.

Your formulas must evaluate to either True or False.

This example will include all the rows that have a value greater than the average of the values in the "Salary" column.

The average salary in this case is: 21,500

There are 6 rows (or cells) that meet this criteria: B4, B8, B9, B11, B14 & B17

Things to Remember

Any range of cells can be used for your criteria as long as it contains at least one column label and at least one cell below specifying some kind of condition.

Named Ranges can be defined and used for either the Database or Criteria cell ranges.

Your criteria should be separated from your data table by at least one blank row or column. It should definitely not overlap.

When data is being evaluated Excel does not distinguish between uppercase and lowercase letters.