How to Use Excel's DPRODUCT Function

By eHow Computers Editor

Rate: (0 Ratings)

Excel's DPRODUCT function is short for "database product." It selects the values in a column of a database that match the specified criteria. DPRODUCT then returns the product of those values just as PRODUCT does. The following steps will show how to use Excel's DPRODUCT function.

Flag Article

Instructions

Difficulty: Moderate

Step1

Learn the syntax of DPRODUCT. It is DPRODUCT(database,field,criteria). Database is an array that specifies the database. Field is the column name or number in the database from which DPRODUCT will take values. Criteria is a cell range that contains the criteria to be used in selecting the rows.

Step2

Check the result returned by DPRODUCT. Multiply all of the values retrieved from the constructed query to obtain a single product. The equivalent non-database function is PRODUCT and calculates its result in the same way.

Step3

Enter the following in the first four columns of an Excel spreadsheet:

Row 1: Tree, Height, Age and Yield;
Row 2: ="=Cherry" and =">13" (nothing in columns three and four);
Row 3: ="=Pear" (nothing in columns two, three and four);
Row 4: Tree, Height, Age and Yield;
Row 5: Apple, 18, 20 and 14;
Row 6: Pear, 12, 12 and 10;
Row 7: Cherry, 13, 14 and 9;
Row 8: Apple, 14, 15 and 10;
Row 9: Pear, 9, 8 and 8;
Row 10: Apple, 8, 9 and 6.

Step4

Interpret the formula =DPRODUCT(A4:D10,"Age",A1:B2) as follows: The database is defined by the array A4:D10. Note the column names in the first row of this array. "Age" is the column name from which the values will be taken. The criteria are given by A1:B2, which indicates that the column named Tree must contain "Cherry" and the column named Height must contain a value greater than 13. DPRODUCT(A4:D10,"Age",A1:B2) will therefore be evaluated as Product(14,15)=210.