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