Use Excel's DGET Function to Summarize Large Tables of Data

Digital Composite Image Of Numbers With Document
Take control of your Excel data with DGET. Chuanchai Pundej / EyeEm / Getty Images

The DGET 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. These functions do this by returning specific information based on one or more criteria chosen by the user.

The DGET function can be used to return a single field of data from a column of a database that matches conditions that you specify. DGET is similar to the VLOOKUP function which can also be used to return single fields of data.

of 02

DGET Syntax and Arguments

Screenshot of Excel showing DGET syntax

A function's syntax is the structure that it must be presented within Excel for the application to properly understand your request. The syntax and arguments for the DGET function are as follows:

= DGET ( 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 (such as #Orders) 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.
of 02

Matching a Criterion with DGET

Screenshot of Excel showing example data

Our example tutorial will use the DGET function to find the number of sales orders placed by a specific sales agent for a given month. Begin by entering the tutorial data as shown above into your Excel spreadsheet.

Selecting the Criteria

To get DGET to only look at data for a specific sales rep we enter the name of an agent under the SalesRep field name in row 3. In cell E3 type the criteria Harry.

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 D6 to F12 in the worksheet to select the range.
  2. Click on the name box above column A in the worksheet.
  3. Type SalesData into the name box to create the named range.
  4. Press the Enter key on the keyboard to complete the entry.
Screenshot of Excel showing the Formula Builder for DGET

Opening the DGET Dialog Box

The Formula Builder provides an easy method for entering data for each of the function's arguments. Opening Formula Builder can be done by clicking on the function wizard button (fx) located next to the formula bar above the worksheet.

  1. Click on cell E4 — the location where the results of the function will be displayed.
  2. Click on the function wizard button (fx) to open the Formula Builder.
  3. Type DGET in the Search window.
  4. Select the DGET function and click the Insert Function button.
  5. Click on the Database line.
  6. Type the range name SalesData.
  7. Click on the Field line.
  8. Type the field name #Orders into the line.
  9. Click on the Criteria line.
  10. Highlight cells D2 to F3 in the worksheet to enter the range.
  11. Click Done to complete the function.
  12. The answer 217 should appear in cell E4 as this is the number of sales orders placed by Harry this month.

#VALUE errors occur 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 D6 to F6 were included in the named range SalesData.