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.