Microsoft Office Tutorials and References
In Depth Information
Adding Only What Matters with DSUM
Adding Only What Matters with DSUM
The DSUM function lets you sum numbers in a database column for just
those rows that match the criteria you specify. For example, take a database
that contains data on individual sale amounts for sales people. The database
range is named Sales. You want to calculate total sales for each of the three
sales representatives. Figure 17-8 shows how this is done. Three criteria
areas are defined in D2:D3, E2:E3, and F2:F3. The DSUM function is entered in
cells E8:E10. The formula in cell E8 is thus:
=DSUM(SALES, “Sale Amount”, D2:D3)
The functions entered in E9 and E10 are identical except for referencing a
different criteria range. The results show clearly that Amy is the sales leader.
Figure 17-8:
Calculating
the sum of
sales with
the DSUM
function.
Going for the Middle with DAVERAGE
The DAVERAGE function lets you find the average, or mean, of a field for just
the rows that match the criteria. For this example you return to the student
database.
Figure 17-9 shows a worksheet in which the average grade for each course
has been calculated using DAVERAGE. For example, cell G22 shows the
average grade for Masters of Philosophy. Here is the formula:
=DAVERAGE(Students,”Final Grade”,F14:G15)
Each calculated average uses a different criteria area. Each area filters the
result by a particular course. In all cases, the criteria area for the Teacher is
left blank and, therefore, has no effect on the results.
Search JabSto ::




Custom Search