Database functions

Click one of the links in the following list to see detailed help about the function.

See Also

• List of worksheet functions (alphabetical)
• List of worksheet functions (by category)

Excel > Function reference > Database and list management

DAVERAGE function

Averages the values in a field (column) of records in a list or database that match conditions you specify.

Syntax

DAVERAGE(database,field,criteria)

Database   is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.

Field   indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as "Age" or "Yield," or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.

Criteria   is the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

Criteria examples

• 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:

• When filtering text data, Excel does not distinguish between uppercase and lowercase characters. However, you can use a formula to perform a case-sensitive search. For an example, see Filtering for text by using a case-sensitive search.

• Multiple criteria in one column
• Multiple criteria in multiple columns where all criteria must be true
• Multiple criteria in multiple columns where any criteria can be true
• Multiple sets of criteria where each set includes criteria for multiple columns
• Multiple sets of criteria where each set includes criteria for one column
• Criteria to find text values that share some characters but not others
• Criteria created as the result of a formula
  • Filtering for values greater than the average of all values in the data range
  • Filtering for text by using a case-sensitive search

• 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.

How?

 (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 (=).)

• 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 formula that you use for criteria must use a relative reference (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.) to refer to the corresponding cell in the first row (in the examples below, C7 and A7).
• All other references in the formula must be absolute references (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.).

Remarks

• You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label for specifying the condition.

For example, if the range G1:G2 contains the column label Income in G1 and the amount 10,000 in G2, you could define the range as MatchIncome and use that name as the criteria argument in the database functions.

• Although the criteria range can be located anywhere on the worksheet, do not place the criteria range below the list. If you add more information to the list, the new information is added to the first row below the list. If the row below the list is not blank, Microsoft Excel cannot add the new information.
• Make sure the criteria range does not overlap the list.
• To perform an operation on an entire column in a database, enter a blank line below the column labels in the criteria range.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• AVERAGE function
• AVERAGEA function
• AVERAGEIFS function
• Database functions

Excel > Function reference > Database and list management

DCOUNT function

Counts the cells that contain numbers in a field (column) of records in a list or database that match conditions that you specify.

The field argument is optional. If field is omitted, DCOUNT counts all records in the database that match the criteria.

Syntax

DCOUNT(database,field,criteria)

Database   is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.

Field   indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as "Age" or "Yield," or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.

Criteria   is the range of cells that contains the conditions that you specify. You can use any range for the criteria argument, as long as the argument includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

Criteria examples

• 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:

• When filtering text data, Excel does not distinguish between uppercase and lowercase characters. However, you can use a formula to perform a case-sensitive search. For an example, see Filtering for text by using a case-sensitive search.

• Multiple criteria in one column
• Multiple criteria in multiple columns where all criteria must be true
• Multiple criteria in multiple columns where any criteria can be true
• Multiple sets of criteria where each set includes criteria for multiple columns
• Multiple sets of criteria where each set includes criteria for one column
• Criteria to find text values that share some characters but not others
• Criteria created as the result of a formula
  • Filtering for values greater than the average of all values in the data range
  • Filtering for text by using a case-sensitive search

• 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.

How?

 (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 (=).)

• 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 formula that you use for criteria must use a relative reference (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.) to refer to the corresponding cell in the first row (in the examples below, C7 and A7).
• All other references in the formula must be absolute references (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.).

Remarks

• You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label for specifying the condition.

For example, if the range G1:G2 contains the column label Income in G1 and the amount $10,000 in G2, you could define the range as MatchIncome and use that name as the criteria argument in the database functions.

• Although the criteria range can be located anywhere on the worksheet, do not place the criteria range below the list. If you add more information to the list, the new information is added to the first row below the list. If the row below the list is not blank, Microsoft Excel cannot add the new information.
• Make sure that the criteria range does not overlap the list.
• To perform an operation on an entire column in a database, enter a blank line below the column labels in the criteria range.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• AVERAGE function
• COUNT function
• COUNTA function
• COUNTBLANK function
• COUNTIF function
• COUNTIFS function
• Database functions
• DAVERAGE function
• DSUM function
• SUM function

Excel > Function reference > Database and list management

DCOUNTA function

Counts the nonblank cells in a field (column) of records in a list or database that match conditions that you specify.

The field argument is optional. If field is omitted, DCOUNTA counts all records in the database that match the criteria.

Syntax

DCOUNTA(database,field,criteria)

Database   is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.

Field   indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as "Age" or "Yield," or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.

Criteria   is the range of cells that contains the conditions that you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

Criteria examples

• 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:

• When filtering text data, Excel does not distinguish between uppercase and lowercase characters. However, you can use a formula to perform a case-sensitive search. For an example, see Filtering for text by using a case-sensitive search.

• Multiple criteria in one column
• Multiple criteria in multiple columns where all criteria must be true
• Multiple criteria in multiple columns where any criteria can be true
• Multiple sets of criteria where each set includes criteria for multiple columns
• Multiple sets of criteria where each set includes criteria for one column
• Criteria to find text values that share some characters but not others
• Criteria created as the result of a formula
  • Filtering for values greater than the average of all values in the data range
  • Filtering for text by using a case-sensitive search

• 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.

How?

 (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 (=).)

• 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 formula that you use for criteria must use a relative reference (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.) to refer to the corresponding cell in the first row (in the examples below, C7 and A7).
• All other references in the formula must be absolute references (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.).

Remarks

• You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label for specifying the condition.

For example, if the range G1:G2 contains the column label Income in G1 and the amount $10,000 in G2, you could define the range as MatchIncome and use that name as the criteria argument in the database functions.

• Although the criteria range can be located anywhere on the worksheet, do not place the criteria range below the list. If you add more information to the list, the new information is added to the first row below the list. If the row below the list is not blank, Microsoft Excel cannot add the new information.
• Make sure that the criteria range does not overlap the list.
• To perform an operation on an entire column in a database, enter a blank line below the column labels in the criteria range.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• COUNT function
• COUNTA function
• COUNTBLANK function
• COUNTIF function
• COUNTIFS function
• Database functions
• DCOUNT function

Excel > Function reference > Database and list management

DGET function

Extracts a single value from a column of a list or database that matches conditions that you specify.

Syntax

DGET(database,field,criteria)

Database   is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.

Field   indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as "Age" or "Yield," or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.

Criteria   is the range of cells that contains the conditions that you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

Criteria examples

• 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:

• When filtering text data, Excel does not distinguish between uppercase and lowercase characters. However, you can use a formula to perform a case-sensitive search. For an example, see Filtering for text by using a case-sensitive search.

• Multiple criteria in one column
• Multiple criteria in multiple columns where all criteria must be true
• Multiple criteria in multiple columns where any criteria can be true
• Multiple sets of criteria where each set includes criteria for multiple columns
• Multiple sets of criteria where each set includes criteria for one column
• Criteria to find text values that share some characters but not others
• Criteria created as the result of a formula
  • Filtering for values greater than the average of all values in the data range
  • Filtering for text by using a case-sensitive search

• 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.

How?

 (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 (=).)

• 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 formula that you use for criteria must use a relative reference (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.) to refer to the corresponding cell in the first row (in the examples below, C7 and A7).
• All other references in the formula must be absolute references (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.).

Remarks

• If no record matches the criteria, DGET returns the #VALUE! error value.
• If more than one record matches the criteria, DGET returns the #NUM! error value.
• You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label for specifying the condition.

For example, if the range G1:G2 contains the column label Income in G1 and the amount $10,000 in G2, you could define the range as MatchIncome and use that name as the criteria argument in the database functions.

• Although the criteria range can be located anywhere on the worksheet, do not place the criteria range below the list. If you add more information to the list, the new information is added to the first row below the list. If the row below the list is not blank, Microsoft Excel cannot add the new information.
• Make sure that the criteria range does not overlap the list.
• To perform an operation on an entire column in a database, enter a blank line below the column labels in the criteria range.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.