How to Use Database Functions in Excel

In this lesson, you will learn about all the database functions in Excel.

Database functions are used to analyze data stored in a database. They have a few common features:

  • Each function has three parameters: database, field and criteria. These parameters indicate the worksheet area used by the function.
  • Except for the GETPIVOTDATA function, the other twelve functions start with the letter D.
  • If you remove the letter D, you may notice that most of the database functions have already appeared in other types of functions in Excel. For example, if from DAVERAGE you remove D, this is well-known AVERAGE function.

General Syntax

Function_name (database, field, criteria)

  • Database – range of cells, where your database is
  • Field – name or numer of column where values are
  • Criteria – your criteria should contain the name of the column and the name of some value from that column.

Let’s see train that with examples. This is an example database:

Excel database functions example

DAVERAGE

This function shows the average of values that meet your criteria. This is an example:

Syntax: =DAVERAGE($A$1:$C$11;3;$A$1:$A$2) or =DAVERAGE($A$1:$C$11;"Sales";$A$1:$A$2)

Excel database functions daverage

DCOUNT

This function shows the count of cells that meet your criteria. This is an example:

Syntax: =DCOUNT($A$1:$C$11,3,$E$2:$E$3) or =DCOUNT($A$1:$C$11,"Sales",$E$2:$E$3)

Excel database functions dcount

DCOUNTA

This function shows the number of noblank cells that meet your criteria. The DCOUNTA function works similarly to the DCOUNT function. This is an example:

Syntax: =DCOUNTA($A$1:$C$11,3,$E$2:$E$3) or =DCOUNTA($A$1:$C$11,"Sales",$E$2:$E$3)

Excel database functions dcounta

DGET

This function shows a single value that meets your criteria. This is an example:

Syntax: =DGET($A$1:$C$11,3,A1:A2) or =DGET($A$1:$C$11,"Sales",A1:A2)

Excel database functions dget

DMAX

This function shows the max value that meets your criteria. This is an example:

Syntax: =DMAX($A$1:$C$11,3,$A$1:$A$2) or =DMAX($A$1:$C$11,"Sales",$A$1:$A$2)

Excel database functions dmax

DMIN

This function shows the min value that meets your criteria. This is an example:

Syntax: =DMIN($A$1:$C$11,3,$A$1:$A$2) or =DMIN($A$1:$C$11,"Sales",$A$1:$A$2)

Excel database functions dmin

DPRODUCT

This function shows the multiplication of values that meet your criteria. This is an example:

Syntax: =DPRODUCT($A$1:$C$11,3,$A$1:$A$2) or =DPRODUCT($A$1:$C$11,"Sales",$A$1:$A$2)

Excel database functions dproduct

DSTDEV

This function estimates the standard deviation of values that meet your criteria. This is an example:

Syntax: =DSTDEV($A$1:$C$11,3,$A$1:$A$2) or =DSTDEV($A$1:$C$11,"Sales",$A$1:$A$2)

Excel database functions dstdev

DSTDEVP

This function estimates standard deviation based on the whole population of values that meet your criteria. This is an example:

Syntax: =DSTDEVP($A$1:$C$11,3,$A$1:$A$2) or =DSTDEVP($A$1:$C$11,"Sales",$A$1:$A$2)

Excel database functions dstdevp

DSUM

This function shows the sum of values that meet your criteria. This is an example:

Syntax: =DSUM($A$1:$C$11,3,$A$1:$A$2) or =DSUM($A$1:$C$11,"Sales",$A$1:$A$2)

Excel database functions dsum

DVAR

This function estimates the variance of values that meet your criteria. This is an example:

Syntax: =DVAR($A$1:$C$11,3,$A$1:$A$2) or =DVAR($A$1:$C$11,"Sales",$A$1:$A$2)

Excel database functions dvar

DVARP

This function estimates the variance of values that meet your criteria based on the entire population. This is an example:

Syntax: =DVARP($A$1:$C$11,3,$A$1:$A$2) or =DVARP($A$1:$C$11,"Sales",$A$1:$A$2)

Excel database functions dvarp