Database functions dmax, dmin, dsum, dproduct
Assuming the following data, let's go through the excel database functions.
| Year | Quarter | Profit | Criteria | |
| 2011 | 1 | 1000 | Quarter | Profit |
| 2012 | 1 | 2000 | 2 | >500 |
| 2011 | 2 | 1340 | ||
| 2012 | 2 | 3400 | Quarter | Profit |
| 2011 | 3 | 2345 | 1 | <3000 |
| 2012 | 3 | 4500 | ||
| 2011 | 4 | 6000 | Quarter | Profit |
| 2012 | 4 | 10000 | 4 | <>5000 |
Excel Function DMAX
The DMAX() function extracts the maximum value from a column of database.
The value extracted is based on a user defined criteria.
The syntax of this function is as follows:
DMAX(database,field,criteria)
| DMAX() | Formula |
| 3400 | =DMAX(L4:N12,"Profit",O5:P6) |
| 2000 | =DMAX(L4:N12,"Profit",O8:P9) |
| 10000 | =DMAX(L4:N12,"Profit",O11:P12) |
Excel Function DMIN
The DMIN() function extracts the minimum value from a column of database.
The value extracted is based on a user defined criteria.
The syntax of this function is as follows:
DMIN(database,field,criteria)
| DMIN() | Formula |
| 1340 | =DMIN(L4:N12,"Profit",O5:P6) |
| 1000 | =DMIN(L4:N12,"Profit",O8:P9) |
| 6000 | =DMIN(L4:N12,"Profit",O11:P12) |
Excel Function DSUM
The DSUM() function adds multiple values from a column of a database based on certain defined criteria.
The syntax of this function is as shown below:
DSUM(database,field,condition)
| DSUM() | Formula |
| 4740 | =DSUM(L4:N12,"Profit",O5:P6) |
| 3000 | =DSUM(L4:N12,"Profit",O8:P9) |
| 16000 | =DSUM(L4:N12,"Profit",O11:P12) |
Excel Function DPRODUCT
The DPRODUCT() function multiplies multiple values from a column of a database based on defined criteria.
The syntax of this function is as shown below:
DPRODUCT(database,field,condition)
| DPRODUCT() | Formula |
| 4556000 | =DPRODUCT(L4:N12,"Profit",O5:P6) |
| 2000000 | =DPRODUCT(L4:N12,"Profit",O8:P9) |
| 60000000 | =DPRODUCT(L4:N12,"Profit",O11:P12) |