Use the Excel DGET function to summarize large data tables

The DGET function is one of the Excel database functions. This group of functions summarizes information from large data tables and returns specific information based on one or more criteria. Use the DGET function to return a single data field that matches the conditions 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.

Content
  1. DGET Syntax and Arguments
  2. Criteria matching DGET
  3. Select criteria
  4. Name the database
  5. Enter the DGET function

DGET Syntax and Arguments

Function syntax is the structure that Excel uses to fulfill your request.

Syntax and arguments for the DGET function:

 = DGET ( базы данных ,  поле ,  Критерии ) 

All database functions have the same three arguments:

  • Database (necessary). Specifies a range of cell references that contain a database. Field names must be included in the scope.
  • Field (necessary). Specifies which column or field the function will use in calculations. Enter an argument by entering a field name or column number.
  • criteria (required): A list of cell ranges containing the specified conditions. The range must contain at least one field name from the database and at least one other cell reference that specifies the condition being evaluated by the function.

Criteria matching DGET

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

To follow this tutorial, enter the data in the image above into a blank Excel sheet.

Select criteria

To have DGET view data only for a specific seller, enter the agent’s name under the name of the SalesRep field in row 3. In cell E3, enter the criteria Harry.

Name the database

Using a named range for large data ranges, such as a database, makes it easier to pass that argument to a function and avoids errors caused by incorrect range selection.

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

  1. Highlight cells D6 F12 on the sheet to select a range.

  2. Place the cursor in the name field.

  3. A type Sales data STRONG>.

  4. Click Enter to create a named range.

Enter the DGET function

You are now ready to get into the DGET function and create a formula along with the function arguments.

  1. Select cell E 4 † Displays the results of the function.

  2. Select Insert function (the fx symbol to the left of the formula bar) to open the Insert Function dialog box. Excel for Mac opens the Formula Builder.

  3. In the text box, type Search function DGET and select TO GO

  4. Select function DGET in the list Select a function and select Okay † Except for Excel for Mac where you choose Insert function

  5. Place the cursor in the Database text box.

  6. Enter Sales dates.

  7. Place the cursor in the Field text box.

  8. Enter #Orders

  9. Place the cursor in the Criteria text box.

  10. Select cells from D2 before F3 on the worksheet to enter a range.

  11. Select Okay † Extract in Excel for Mac wherever you want Finished

  12. Answer 217 appears in cell E4.

  13. This is the number of orders made by Harry.

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

Leave a Reply

Your email address will not be published.