Dsum Function in Excel 2007
Database functions in Excel like Dsum are powerful analytical and calculation tools.
The function takes 3 arguments or parameters and then adds the numbers in the field (column) of records in the database that match the specified criteria or condition.
The 3 parameters are:
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 “Designation” or “Salary” or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column (Designation in our example), 2 for the second column (Salary in our example), 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. In our example the criteria range is A9 to C10.
Implementing Database functions:
- Select the labels of the columns, copy and paste them 3-4 rows below your data or 3-4 columns away from your data.
- Now specify your criteria like ‘Manager’ under the designation label and or some salary value under the salary label as shown in our example.
- Next click inside a cell where you wish to get your reults. Type the ‘=dsum(…….)’ formula by providing the arguments as shown in the video. The first argument is your database. Select the data range including the labels. You can also assign a ‘name’ to the range like ‘mydata’ and use it.
- Then define the ‘field’ using the column label like ‘Salary’ or a column number. The left-most column is designated as 1, then 2 2, and so on
- The final step is to define the criteria range which includes the headers that you copied and pasted and the criteria values.
- After closing the bracket press enter and the result will be displayed.
Excel Database Functions: DAVERAGE and DSUM