The DPRODUCT function is one of several database functions in Microsoft Excel. You might be wondering how a function in Excel is being called a database function when Excel is only a spreadsheet. We’ll see that in a moment.

As the name suggests DPRODUCT or Database Product multiplies numeric field values in a column. This is just the simple definition of the function. DPRODUCT can do more complex searches based on criteria we provide. Let us study DPRODUCT in more detail.

Since we have a function that multiplies values in a column let us consider a different type of Excel spreadsheet. In this spreadsheet are details of portable water tanks and trailers. Let us say we need to find the volume of these trailers or water tanks.  Now as we know volume is calculated using length, breadth and height or length x depth x height.  Also let us assume that the spreadsheet contains two columns, one for the tanks and trailers and the other for the dimensions. So each tank or trailer will have three entries (duplicates) in the first column and in the second column three entries one each for the length, breadth and height.  Our objective is to determine the volume of a tank or trailer and this can be done using DPRODUCT.

Like all other Microsoft Excel Database Functions, to use DPRODUCT, there are certain pre-requisites. Do you recall just a while ago we mentioned that DPRODUCT is a database function? So where is our database? Ok, here goes. We actually don’t use a Database but we present the data inside the spreadsheet in the form of a database. Didn’t get it? Read on.  So what does a database typically have? A database has fields and records.  A record is a group of fields.  So all we need to do in Excel is to present this data in the form of fields and records. Many of you are Excel consultants and have actually been doing this ever since you used Excel but were unaware of it. Remember your columns and column headers in your spread sheet. These become the fields. A row in your spreadsheet becomes the record. But to use these columns and rows with DPRODUCT we’ll have to remember some fundamental and mandatory rules without which DPRODUCT will not function properly.

  1. Your columns must have headers.
  2.  No empty or blank cells are allowed. So all cells MUST have data in them. ABSOLUTELY no blank rows either.
  3. There must not be data in cells adjacent i.e. touching the cells we want to include in the database.

The syntax of the DPRODUCT function is as follows – DPRODUCT(database, field, criteria). Let’s explore the parameters one by one.

Database – This is what we have been discussing so far.  The fields for our example are Trailer/Tank and Dimensions.
Field – Is the field or column where we want to multiply the values , in our case the Dimensions field.

Criteria – Now here is where you can add other conditions, one or more.  To specify criteria we have to do some additional steps. In an unused portion of your spreadsheet create an identical table, this time with two (for clarity) row cells Field Name and Criteria, exactly one on top of the other. This is a mandatory condition. So the row of headers will contain the following headers, Trailer/Tank and Dimensions. You will fill in the cell just below Field Name with the word Criteria. This table must not be touching any cells of the main database table.

We’ll now look at some examples of DPRODUCT computations using criteria.

First let us name the database here so that we may just have to refer to it by name instead of selecting it each time. To name the database select all the fields and all the rows (Ctrl+down arrow). Next name the selection by entering name in the Name field (top left) and this becomes the name of our database. Let’s say our database is named, TrailerTanks.

So at the cell where we want to add the DPRODUCT function enter =DPRODUCT(TrailerTanks,

Next, click on the cell header of the column where you are going to multiply the values. In our example we click on the Dimensions column header as we want to multiply the values. The cell is automatically inserted into formula. Alternatively you can also enter the name of the column header as “Dimensions” or double click or use F9.

Next we need to input the criteria. For criteria we need to click on the field we wish to use in the criteria. Here we can enter a Tank’s or Trailer’s name. Say we have a Tank called ApartmentTankA whose volume we want to compute. We enter “ApartmentTankA” in the cell below the header cell that is titled Trailer/Tank in the separate data table we created for field and criteria. Now click the field Trailer/Tank and drag in the value for the field for criteria also from the Criteria row where we had entered “ApartmentTankA”.

The DPRODUCT function will now have the criteria data embedded inside the formula and the volume of the tank is computed by DPRODUCT function by multiplying the three values in the Dimensions column (for height, length and breadth) and the value is displayed in the cell.

The final formula will look something like this – =DPRODUCT(TrailerTanks, “Dimensions”, I14:I15)
To find out the volume of another tank or trailer simply replace the “ApartmentTankA” by the name of the tank or trailer of your choice and DPRODUCT will instantly compute the volume.

Note that there is yet another method of inserting the DPRODUCT function into the Microsoft Excel spreadsheet. For this first select the cell where you would like to insert the function. Then from the Formula tab, the Function Library group, choose Insert Function. The Insert Function dialog opens. Here select the DPRODUCT function from the Select a Function box. Note that you need to ensure you have selected the Database category located just above the Select a Function box.  A new Function Arguments dialog is displayed where you can select the Database, Field and Criteria by clicking the buttons (located adjacent to the corresponding fields) which will take the control back to your spreadsheet and back to the dialog by inserting the selected ranges into the fields. When done click OK and your function is all set. As a confirmation your cell will now have the value computed by DPRODUCT inside. See, you do not need to be an experienced Excel consultant to use this, any motivated business user can do so as well.