1. Computing

Excel DSUM Function Tutorial

By

Sum Data in an Excel Database
Excel DSUM Function Tutorial

Excel DSUM Function Tutorial

© Ted French

DSUM Function Overview

The DSUM function is one of Excel's database functions.

This group of functions is designed to make it easy to summarize information from large tables of data.

They do this by returning specific information based on one or more criteria chosen by the user.

The DSUM function can be used to add up or sum the values in a column of data that meet the set criteria.

DSUM Syntax and Arguments

The syntax for the DSUM function is:

= DSUM ( database, field, criteria )

All database functions have the same three arguments:

Database: (required) Specifies the range of cell references containing the database. The field names must be included in the range.

Field: (required) Indicates which column or field is to be used by the function in its calculations. Enter the argument either by typing the field name in quotes - such as "Radius" - or enter the column number - such as 3.

Criteria: (required) Lists the range of cells containing the conditions specified by the user. The range must include at least one field name from the database and at least one other cell reference indicating the condition to be evaluated by the function.

Example Using Excel's DSUM Function - Matching Two Criteria

Click on the image above for a larger view of this example.

This example will use DSUM to find the amount of sap collected as listed under the Production column.

The criteria used to filter the data in this example will be the type of maple tree.

We will find the amount of sap collected only from Black and Silver maples.

Entering the Tutorial Data

Note: The tutorial does not include formatting steps. Information on worksheet formatting options is available in this Basic Excel Formatting Tutorial.

  1. Enter the data table as seen in the image above into cells A1 to E11 - Or copy the data from this link into a blank worksheet

  2. Copy the field names in cells A2 to F2

  3. Paste the field names in cells A13 to E13 - these will be used as part of the Criteria argument

Selecting the Criteria

To get DSUM to only look at data for Black and Silver maples trees we can enter the tree names under the Maple Tree field name.

To find data for more than one tree we need to enter each tree name in a separate row.

  1. In cell A14 type the criteria Black

  2. In cell A15 type the criteria Silver

  3. In cell D16 type the heading Gallons of Sap: to indicate the information we will be finding with DSUM

Naming the Database

Using a named range for large ranges of data such as a database can not only make it easier to enter this argument into the function, but it can also prevent errors caused by selecting the wrong range.

Named ranges are very useful if you use the same range of cells frequently in calculations or when creating charts or graphs.

  1. Highlight cells A2 to E11 in the worksheet to select the range

  2. Click on the name box above column A in the worksheet

  3. Type Trees into the name box to create the named range

  4. Press the Enter key on the keyboard to complete the entry

Opening the DSUM Dialog Box

A function's dialog box provides an easy method for entering data for each of the function's arguments.

Opening the dialog box for the database group of functions is done by clicking on the function wizard button (fx) located next to the formula bar above the worksheet - see image above.

  1. Click on cell E16 - the location where the results of the function will be displayed

  2. Click on the function wizard button (fx) icon to bring up the Insert Function dialog box

  3. Type DSUM in the Search for a function window at the top of the dialog box

  4. Click on the GO button to search for the function

  5. The dialog box should find DSUM and list it in the Select a function window

  6. Click OK to open the DSUM function dialog box

Completing the Arguments

  1. Click on the Database line of the dialog box

  2. Type the range name Trees into the line

  3. Click on the Field line of the dialog box

  4. Type the field name "Production" into the line - be sure to include the quotation marks

  5. Click on the Criteria line of the dialog box

  6. Drag select cells A13 to E15 in the worksheet to enter the range

  7. Click OK to close the DSUM function dialog box and complete the function

  8. The answer 152 - indicating the number of gallons of sap collected from Black and Silver maple trees - should appear in cell E16

  9. When you click on cell C7 the complete function
    = DSUM ( Trees, "Production", A13:E15) appears in the formula bar above the worksheet

Note: If we wanted to find the amount of sap collected for all trees, we could use the regular SUM function, since we do not need to specify criteria to limit what data is used by the function.

Database Function Errors

#Value: Occurs most often when the field names were not included in the database argument.

For the example above, be sure that the field names in cells A2:E2 were included in the named range Trees.

©2014 About.com. All rights reserved.