Because all database functions—except the GETPIVOTDATA() function—have the same arguments, these functions will be explained at the beginning of this chapter. For database functions, you have to specify three arguments:
database The database argument specifies the cell range that makes up the list or database. In the example used in this chapter, the range A1:F7008 has been given the range name Database.
field The field argument indicates which column is to be used in the function. Enter the column label enclosed in quotation marks, as in “country/region”, “sales”, or “company”. You can also enter a number that represents the position of the column within the list, such as 1 for the first column or 2 for the second column.
criteria The criteria argument indicates a cell range containing the field names and the filter criteria that will be used for the function. You can specify any search criteria for this argument. However, the argument has to include at least one column label and at least one cell below the column label in which you specify a condition for the column.
Note
You cannot enter criteria as array expressions directly into the function. You need to set up an area in the spreadsheet where the criteria range is specified, and reference this range as the argument in the database function.
An example of search criteria and their functionality is outlined in the example that follows.
In the database example, for simplicity, the examples are set up to the side of the raw data.
Assume that you want to evaluate sales for particular articles within different countries:
Set up the criteria definitions to the right of the data set. You need the three field names Country/Region, Product, and Sales. To ensure that you have exactly what has been typed in the database, copy the column headings and paste them to the right of the table.
Below the field names, enter a condition. A field could be quoted as text together with the column heading in quotation marks, such as in “USA”, “1500” or “U*”; as well as logical operators and expressions such as “>k” or “<2000”. For countries and products, you can again copy and paste from entries in the database to ensure that you have typed the entry correctly.
Use the range containing the field names and the filter conditions in the database functions. The following example outlines the use of the criteria ranges.
Note
In the search criteria, the asterisk (*) is used as a wildcard for any characters, and the question mark (?) is used as wildcard for a single character.
Figure 13-2 specifies the search criteria: Country/Region is Mexico AND the product is Geitost AND the Sales value is greater than $1,500.
You can also use OR conditions by entering the criteria in multiple rows (see Figure 13-3).
In this example, the criteria specifies Mexico Geitost orders greater than $1500 or Mexico Pavlova orders greater than $1,500.