Microsoft Office Tutorials and References
In Depth Information
Creating a Simple Criteria Range for Database Functions
the Miracle Version of the Criteria Range, later in this chapter.
Learning how to create powerful criteria ranges enables you to un-
lock the powerful potential of the database functions. Several ex-
amples are provided in the following sections.
Creating a Simple Criteria Range for Database Functions
Although a criteria range needs only one field heading from the database, it
is just as easy to copy the entire set of headings to a blank section of the
worksheet. In Figure 12.51 , for example, the headings in A17:I17, along with at
least one additional row, create a criteria range.
In Figure 12.51 , you see results of the 11 database functions for a simple cri-
teria where the customer is Best Paint Inc. Each formula specifies a data-
base of $A$23:$I$5024. The field is H23, which is the heading for Revenue. The
criteria range is A17:I18. In this example, the criteria range could have eas-
ily been A17:A18, but the A17:I18 form enables you to enter future criteria
without specifying the criteria range again.
Using a Blank Criteria Range to Return All Records
This is a trivial example, but if the second row of the criteria range is com-
pletely blank, the database function returns the total of all rows in the
data set. As shown in Figure 12.52 , this is $256.6 million. This is equivalent
to using the SUM function.
Figure 12.52.
Figure 12.52. If the second row of the criteria is blank, the result reflects
If the second row of the criteria is blank, the result reflects
all rows.
all rows.
Search JabSto ::




Custom Search