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

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 summarizes information from large tables of data by returning specific information based on one or more criteria. Use the DGET function to return a single field of data that matches conditions that you specify.

The instructions in this article apply to Excel 2019, 2016, 2013, 2010, 2007; Excel for Office 365, Excel Online, Excel for Mac, Excel for iPad, Excel for iPhone, and Excel for Android.

DGET Syntax and Arguments

A function's syntax is the structure Excel uses to execute your request.

The syntax and arguments for the DGET function are:

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 or the column number.
  • Criteria (required): Lists the range of cells containing the specified conditions. 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.

Match a Criterion with DGET

The example shown in this tutorial uses the DGET function to find the number of sales orders placed by a specific sales agent for a given month.

A screenshot of Excel showing example data used for the DGET function

To follow along with this tutorial, enter the data shown in the above image into a blank Excel worksheet.

Select the Criteria

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

A screenshot showing the criteria set for the DGET function in Excel

Name the Database

Using a named range for large ranges of data such as a database makes it easier to enter this argument into the function and it prevents errors caused by selecting the wrong range.

Named ranges are useful when the same range of cells is frequently used in calculations or when creating charts or graphs.

  1. Highlight cells D6 to F12 in the worksheet to select the range.

  2. Place the cursor in the Name Box.

  3. Type SalesData.

    A screenshot showing how to create a named ranged in Excel
  4. Press Enter to create the named range.

Enter the DGET Function

Now you're ready to enter the DGET function and create the formula along with the function arguments.

  1. Select cell E4. This is where the results of the function will display.

  2. Select Insert Function (the fx symbol located to the left of the Formula Bar) to open the Insert Function dialog box. In Excel for Mac, the Formula Builder opens.

  3. In the Search for a function text box, enter DGET and select GO.

    A screenshot showing how to search for the DGET function in Excel
  4. Select the DGET function in the Select a function list and select OK. Except in Excel for Mac, where you select Insert Function.

  5. Place the cursor in the Database text box.

  6. Enter SalesData.

  7. Place the cursor in the the Field text box.

  8. Enter #Orders.

  9. Place the cursor in the Criteria text box.

  10. Highlight cells D2 to F3 in the worksheet to enter the range.

    A screenshot showing how to enter the DGET function in Excel
  11. Select OK. Excerpt in Excel for Mac, where you select Done.

  12. The answer 217 appears in cell E4.

    A screenshot showing the result of the DGET function in Excel
  13. This is the number of sales orders placed by Harry.

#VALUE errors occur when the field names are not included in the database argument. For this tutorial, the field names in cells D6 to F6 must be included in the named range SalesData.