Microsoft Office Tutorials and References
In Depth Information
Being Productive with DPRODUCT
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.