Database functions

Excel has several database functions that can be used to analyze data stored in tables or databases. A table or a database is a range containing at least one column with data and two or more rows. The first row in each column contains the column heading, also called the fieldname. It's common to define a name to the range containing the table or database, this makes it easier to understand the formulas later. All of the database functions uses three arguments : database, field and criteria range. The database functions are mostly named the same as the statistical functions, with the letter D in front of the function name, e.g. DSUM, DCOUNT.

All the database functions has the following syntax : Dfunction(database,field,criteriarange).
The argument database is filled in with the cell references or the userdefined name to the range that contains the data.
The argument field is filled in with the cell reference to the cell in the database that contains the field name you wish to use in the function. It's also possible to use the field name as plain text surrounded by quotation marks ("). You can also use the field number, the first column in the table or database is field number 1, independent of which column number it has in the worksheet.
The argument criteriarange is filled in with the cell reference to the range in the worksheet that contains the criterias that is to be used by the function. The criteria range has to contain at least 1 column and 2 rows. The first row has to be filled in with field names from the database, or formulas that returns field names from the table or database. It's not necessary to use all the field names from the database, only those field you want to use to perform the query are necessary. If you include a field name twice you can perform a query within an interval of the data values.

In the following example the database range is displayed with an yellow background color, the criteria range is displayed with a green background color. The formulas that are used is displayed as text in column C.

Example 1 : (Norwegian example picture : DANTALLA = DCOUNTA, DSUMMER = DSUM)

This example shows how many values in column D that is greater than 250, together with the total sum for the values.
When you want to count something in a table or database you ought use a field that always will be filled in when you register a new record in the table or database.

Example 2 : (Norwegian example picture : DANTALLA = DCOUNTA, DSUMMER = DSUM)

This example shows how you can perform a query within an interval by using a field name twice in the criteria range. The example shows how many dates in column C that is equal to or greater than 1.1.1950 and less than 1.1.1960, together with the total sum for for the values in column D.

Example 3 : (Norwegian example picture : DANTALLA = DCOUNTA, DSUMMER = DSUM)

This example shows how you can use multiple rows in the criteria range to perform a more advanced query. The example shows how many names in column B the starts with NO or H, together with the total sum for the values in column D.

Example 4 : (Norwegian example picture : DANTALLA = DCOUNTA, DSUMMER = DSUM)

This example shows how you can perform a query on nonblank fields (cells that are filled in). The example shows how many cells in column B that are filled in, together with the total sum for the values in column D.

Example 5 : (Norwegian example picture : DANTALLA = DCOUNTA, DSUMMER = DSUM)

This example shows how you can perform a query on blank cells (empty cells). The example shows how many cells in column B that is empty, together with the total sum for the values in column D.

Document last updated 1998-11-25 12:44:52

User comments:

Geri from Hungary wrote (2006-12-05 14:21:31 CET):

It is possible to replace dates with cells...
Thank you, Ole!
This little formula solves my problem in creating variable date range depending on manual entry in a certain cell.
:))

Ole P. from Norway wrote (2006-12-05 09:41:41 CET):

Re: Is it possible to replace dates with cells?
Change the formula in the cell containing the query criteria to something like this:
=">="&C3

Geri from Hungary wrote (2006-12-04 14:36:10 CET):

Is it possible to replace dates with cells?
I found very useful your examples, especially the one below:

'This example shows how you can perform a query within an interval by using a field name twice in the criteria range. The example shows how many dates in column C that is equal to or greater than 1.1.1950 and less than 1.1.1960, together with the total sum for for the values in column D.'

Is it possible to replace dates with cells? I mean, to add ex. C3 as criteria instead of 1.1.1960... I tried to simply change from >=1.1.1960 to >=C3, but excel doesn't like it...