DPRODUCT Excel Formula

Author Zaheer    Category Formulas     Tags ,

Microsoft Excel has become one of the most-used spreadsheet which allows you to compute data, create graphs and pivot tables, and has Visual Basic for Applications. Excel also has Database Functions to aid users in database activities. Through this function, the user can do basic operations which include summation and counting wherein you can employ data from your database.

One of Database Functions is Excel DPRODUCTs function which computes column that contains data that will satisfy the user’s specific criteria. Syntax of a DPRODUCT function include:

 =DPRODUCT (database,field,criiteria).

Syntax or sequence of arguments of DPRODUCT function are as follows:

  • The database argument refers to range of cell which contains database.
  • Field name must be specified in the upper most part of the row. Field argument is the field that you that you like to look for the product. This is the column in database that has the fields name or numbers which are enclosed in parenthesis.
  • The criteria arguments are a series of cell that has the criteria. These criteria are used to identify which records will becomposed in the computation. Moreover, the given ranges can include a criteria or group of criteria. This range cells must have field names in cell and below it is the condition of the field.

For multiple criteria on a column input the criteria immediately below and split different row of ranges of criteria. For the Boolean logic which is Sales person =”Davolio” or Salesperson =”Buchanan”. Open a blank Excel spreadsheet or worksheet and input following data and values; cell A1 input Type, cell B1 input Salesperson, cell C1 input Sales.

Copy the labels and paste it on cell A6:C6 and below the type input the labels as follows; cell A7 input Beverages, cell A8 input Meat, cell A9 input Produce, and cell A10 input Produce. Below the Salesperson input the following as follows; cell B7 input Suyama, cell B8 input Davolio, in cell B9 input Buchanan, and in cell B10 input Davolio. Now, below Sales column input the following values as follows; cell C7 input $5122, in cell C8 input $450, in cell C9 input $6328, and in cell C10 input $6544.

The data ranges A6:C10, B1:B3 criteria range shows the A8:C10 that has the ” “Buchanan orDavolio” in Sales person. In cell B2 input =Davolio and in cell B3 input =Buchanan.

As an example for the multiple columns that contains multiple criteria which should be true use the Boolean logic of =”Produce” and Sale>1000.

For you to be able to get rows which have multiple criteria that are also in multiple column just input criteria in same location of the rows of given range of criteria.

Open a blank excel worksheet or spreadsheet and input following data and values as follows; cell A1 input Type, in cell B1 input Salesperson, and in cell C1 input Sales. Copy the labels and paste it on cell A6:C6. Below the Types input the following as follows; cell A7 input Beverages, cell A8 input Meat, cell A9 input Produce, and in cell A10 input Produce. Below the Salesperson input following data; input Suyama in cell B7, input Davolio in cell B8, input Buchanan in cell B9, and input Davolio in cell B10. for the values of the Sales input the following; input $5122 in cell C7, input $450 in cell C8, input $6328 in cell C9, and input $6544 in cell C10. In cell B2 input =Davolio and in cell B3 input =Buchanan.

The cells A6:C10 are the data cells while the A1:C2 cells has the criteria that has the “Produce” in column which contains the “Type”. In cell A9:C10 are the columns of the Sales, which has a value more than 1,000 dollar.

These are just two of the multiple complex criteria. In using criteria in calculating values which are results of formula it must be evaluates as true / false. Input the formula in the usual manner. Also, be sure that you dont use similar column labels for criteria. Lastly, relative reference must be employed as criteria which will correspond for first row.

Post comment