Microsoft Office Tutorials and References

In Depth Information

**Other Database Functions**

name Age with quotation marks unless the ﬁ eld has been assigned a name through the

Name box in the formula bar.

The third value, Q2:Q3, is the criteria range for the grade count. In the case of the

DCOUNT function, it is required that you select a numeric ﬁ eld to count even though

the value of the numeric ﬁ eld itself is not used.

Other database functions that are similar to the functions described in previous

chapters include the DMAX, DMIN, and DSUM functions. For a complete list of the

database functions available for use with a table, click the Insert Function box in the for-

mula bar. When Excel displays the Insert Function dialog box, select Database in the ‘Or

select a category’ list. The ‘Select a function’ box displays the database functions. If you

click a database function name, Excel displays a description of the function above the OK

button in the Insert Function dialog box.

Quick Reference

For a table that lists how

to complete the tasks

covered in this topic

using the mouse, Ribbon,

shortcut menu, and

keyboard, see the Quick

Reference Summary at

the back of this topic, or

visit the Excel 2007 Quick

Reference Web page

(scsite.com/ex2007/qr).

The SUMIF and COUNTIF functions are useful when you want to sum values in a range

or count values in a range only if they meet criteria. The range need not be a table. For

example, assume you want to sum the YTD sales of the sales reps that have a grade of A.

Or, assume you want to count the number of male sales reps. The ﬁ rst question can be

answered by using the SUMIF function as follows:

=SUMIF(J9:J21,”A”,H9:H21)

where the ﬁ rst argument J9:J21 is the range containing the numbers to add, the second

argument “A” is the criteria, and the third argument H9:H21 is the range containing the

cells with which to compare the criteria.

The second question can be answered by using the COUNTIF function as follows:

=COUNTIF(B9:B21,”M”)

where the ﬁ rst argument B9:B21 is the range containing the cells with which to compare

the criteria.

To Use the SUMIF and COUNTIF Functions

The following steps enter identiﬁ ers and these two functions in the range O8:R9.

1
Enter
Grade A YTD Sales Sum = = = =>
in cell O8.

2
Enter
Male Sales Rep Count = = = = =>
in cell O9.

3
Select cell R8 and then enter
=SUMIF(j9:j21,”A”,h9:h21)
as the function.

4
Select cell R9 and then enter
=COUNTIF(b9:b21,”M”)
as the function.

5
Select the range O8:R9 and then click the Bold button on the Ribbon.

6
Select cell R8, click the Comma Style button on the Ribbon, and then click the Decrease

Decimal button on the Ribbon twice.