Microsoft Office Tutorials and References

In Depth Information

**Being Productive with DPRODUCT**

Figure 17-14:

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-sized shirts.

Four cells each contain DPRODUCT, and within each 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 in Figure 17-14 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.