Specifying Criteria

This explains how the criteria that is passed to a database function can be defined.
The method used to specify your criteria is the same method used when using the Advanced AutoFilter.
As with all the functions you can reference the cells either using a named range or using the cell reference.
For the purpose of these examples, the cell range "B2:F17" has the named range "Database_Range".

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
The database function we have used here is DCOUNT which takes three arguments: DCOUNT(database, field, condition).

The second argument "field" can be specified as a column label enclosed between double quotation marks, such as "Name" or "Age".
Or as a number representing the position of the column within the table from left to right.
In this table "Age" is column number 2.

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" in the "Name" column
OR "James" in the "Name" column
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 have a value <19,000 in the "Salary" column.
There are 3 rows (or cells) that meet this criteria: B3, B10 & B15

This example will include all the rows that:
start with a "J" in the "Name" column
AND have a date > 01/10/2020 in the "Date" column.
A date string will not be recognised. You must use the DATE function.
There are 5 rows (or cells) that meet this criteria: B3, B7, B10, B13 & 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 - 1 Criteria

This example will include all the rows that:
start with a letter "J" in the "Name" column
AND have a date < 01/10/2020 in the "Date" column
AND have a date > 01/12/2019 in the "Date" column.
There are 3 rows (or cells) that meet this criteria: B3, B13 & B15

3 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 letter "J" in the "Name" column AND have a value >19,000 in the "Salary" column AND have a value < 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.
In this example we will be using the column heading 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.
The D3 refers to the cell in the column you want to use as a reference point for the formula.
In this case because we want to compare the values in the Salary column we reference the first cell in this column.
When the field is "Salary", 6 rows (or cells) meet the criteria: C4, C8, C9, C11, C14 & C17.
When the field is "Age", 5 rows (or cells) meet the criteria: B4, B8, B9, B14 & B17. Note B11 contains text.

Important

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.
Although the database functions are useful when you have a large amount of information, Pivot Tables are also worth considering.
It is also possible to use the database function using the data in a pivot table.