How to Use the DPRODUCT Function in an Excel Database

By Ken Bluttman

DPRODUCT multiplies values that match the criterion in an Excel database. This is powerful but also able to produce results that are not the intention. In other words, it’s one to thing to add and derive a sum. That is a common operation on a set of data.

Looking at the following figure, you can see that the total sales for Jack Bennet, $79,134, are the sum of three amounts: $43,234, $12,450, and $23,450. If multiplication were applied to the three amounts, the answer (the product) would be $12,622,274,385,000. Oops! That’s over 12 trillion dollars!

Calculating the sum of sales with the DSUM function.

Calculating the sum of sales with the DSUM function.

DPRODUCT multiplies and, therefore, is not likely to be used as often as a function like DSUM, but when you need to multiply items in a database, DPRODUCT is a tool of choice.

The following figure shows a situation in which DPRODUCT is productive. The database area contains shirts. For each shirt size, there are two rows: the price per shirt and the number of shirts that are packed in a carton. The cost for a carton of shirts is, therefore, the product of the price per shirt times the number of shirts. There are four shirt sizes, each with its own price and carton count.

Calculating the total costs of cartons filled with shirts.

Calculating the total costs of cartons filled with shirts.

To be sure, you work with just one size per use of DPRODUCT, four criteria areas are set up — one for each size. Any single criteria area has the Shirt Size heading and the actual shirt size, such as Medium. For example, D8:D9 contains the criteria for medium-size shirts.

Four cells each contain DPRODUCT, and within each cell, the particular criteria area is used. For example, cell E18 has this formula:

=DPRODUCT(A1:C9, "Value", D8:D9)

The database range is A1:C9. Value is the field the function looks in for values to multiply, and the multiplication occurs on values for which the shirt size matches the criteria.

A worksheet set up like the one shown is especially useful when new data are occasionally pasted into the database area. The set of DPRODUCT functions will always provide the products based on whatever data are placed in the database area. This particular example of DPRODUCT shows how to work with data in which more than one row pertains to an item. In this case, each shirt size has a row showing the price per shirt and a second row showing the number of shirts that fit in a carton.