Microsoft Office Tutorials and References
In Depth Information
More About the Criteria Range
More About the Criteria Range
The comparison criteria in the criteria range determine the records that will pass the test
when the Advanced Filter dialog box is used. This section describes examples of different
comparison criteria.
A Blank Row in the Criteria Range
If the criteria range contains a blank row, it means that no comparison criteria have
been defi ned. Thus, all records in the table pass the test. For example, the blank row in
the criteria range shown in Figure 5–62 means that all records will pass the test.
all records will
pass test because
no comparison
criteria defi ned
Figure 5–62
Using Multiple Comparison Criteria with the Same Field
If the criteria range contains two or more entries below the same fi eld name, then
records that pass either comparison criterion pass the test. For example, based on the cri-
teria range shown in Figure 5–63, all records that represent sales reps with a State value of
MT or NM will pass the test.
State = MT
or State = NV
Figure 5–63
The Criteria Area
When you add items in
multiple rows to a criteria
area, you must redefi ne
the range of the name
Criteria before you use
it. To redefi ne the name
Criteria, click the Name
Manager button on the
Formulas tab on the
Ribbon. When Excel
displays the Name
Manager dialog box,
select Criteria in the list
and then click the Delete
button. Next, select the
new Criteria area and
name it Criteria using
the Name box.
If an AND operator applies to the same fi eld name (Age > 50 AND Age < 55),
then you must duplicate the fi eld name (Age) in the criteria range. That is, add the fi eld
name Age in cell K2 to the right of Grade and then adjust the range assigned to the name
Criteria by using the Defi ne Name command on the Formulas tab on the Ribbon.
Comparison Criteria in Different Rows and below
Different Fields
When the comparison criteria below different fi eld names are in the same row, then
records pass the test only if they pass all the comparison criteria. If the comparison criteria
for the fi eld names are in different rows, then the records must pass only one of the tests.
For example, in the criteria range shown in Figure 5–64, female sales reps OR outside
sales reps pass the test.
 
 
Search JabSto ::




Custom Search