PlanMaker provides database functions that allow you to query and evaluate a cell range that is structured like a database.
All database functions expect the following syntax:
FUNCTION_NAME(DatabaseArea, Column, SearchCriteriaArea)
For example, the DSUM function returns the sum of all cells in the DatabaseArea cell range that are in the specified Column and match the conditions defined in the SearchCriteriaArea cell range.
To use the database functions, proceed as follows:
Creating a database area
A database area is any cell range containing the data to be evaluated. It has to be structured as follows:
▪Its first row must contain labels for the database columns (e.g., Amount, Price, etc.).
▪The data has to be arranged row by row (one row for each record).
The rows of the database area represent what is called a record in database applications, with the cells being the fields of the database.
Creating a search criteria area
The search criteria area is a cell range containing conditions that the database records have to match.
The search criteria area has to be structured as follows:
▪Important: The search criteria area has to be located above the database area. The criteria area must not overlap the database area.
▪The first row must also contain column labels. If, for example, you want to define criteria for the columns that you have assigned the labels Amount and Price in the database area, you must also enter Amount and Price here.
▪The conditions to be met must be entered below the labels. You can enter either single values or comparisons like ">42" or "<10".
▪If multiple conditions have been entered in the same row, the conditions in column 1 and column 2 and column 3, etc., must be met.
▪If multiple rows with conditions have been entered, the conditions in row 1 or row 2 or row 3, etc., must be met.
Using database functions
After you have filled in the database area and search criteria area, you can use the database functions.
Here are some examples. Let's say you've created the following worksheet:
For example, the following calculations can be performed here:
DCOUNT(A11:C16, , A2:A3) returns 2. This formula counts the number of records matching the condition Color = blue.
DCOUNT(A11:C16, , A6:B7) returns 3. This formula counts the number of records matching the condition (Amount > 25 and Amount < 75).
DCOUNT(A11:C16, , A6:C8) returns 4. This formula counts the number of records matching the condition (Amount > 25 and Amount < 75) or (Color = red).
DSUM(A11:C16, "Amount", A2:A3) returns 150. This formula calculates the sum of the values in the "Amount" column for all records matching the condition Color = blue.
DSUM(A11:C16, 1, A2:A3) returns 150. This is the same as above, except that the column was specified by its relative number instead of its label.