Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Criteria Range
The criteria argument is a worksheet range that contains the criteria—it is not the actual criteria. At the simplest, for a single criterion, this is a two-cell range that is one column wide and two rows high. The upper cell contains the field name that the criterion applies to; the lower cell contains the value to match. For example, to match records where the Author field contains "Patrick O’Brian," you would put the text "Author" in a cell and the text "Patrick O’Brian" in the cell directly below. This two-cell range—for example, A11:A12—would be the third argument to the DSUM() function (see Figure 2). You can see the DSUM() function’s argument in the formula bar.
)
Figure 2 Using a database function with a criterion
Note that you would generally not put your criteria range or anything else in the same columns below the database because they might interfere if you add more records. You usually should place your criteria range(s) in some out-of-the-way location, in which they are not likely to be changed inadvertently. Some Excel developers like to place them on a separate worksheet.
If you want to match two or more values, add more values to the criteria range below the first one. For example, the criteria range shown in Figure 3matches all records in which the Author field contains "Patrick O’Brian" or "Zadie Smith." Of course, the expanded criteria range, A11:A13, must be passed to DSUM() as the third argument.
)
Figure 3 A criterion that matches either of two values for the Author field
If you want to define criteria for two or more fields, place them in adjacent columns. The example in Figure 4 finds records in which the Author field contains "Zadie Smith" and the Published field contains "Modern."
)
Figure 4 Criteria that must match values in two database fields