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.
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
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:
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:
where the ﬁ rst argument B9:B21 is the range containing the cells with which to compare
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.