PlanMaker provides database functions that allow you to query and evaluate a cell range that is structured like a database.
Database functions expect the following syntax:
FUNCTION_NAME(DatabaseArea, Column, CriteriaArea)
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 CriteriaArea cell range.
To utilize database functions, proceed as follows:
Creating a database area
A database area can be any cell range containing data to evaluate. 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 criteria area
The criteria area is a cell range containing conditions that the database records have to match.
It has to be structured as follows:
▪Important: The 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. For example, if you want to define criteria for the columns that you have given the labels Amount and Price in the database area, you must also enter Amount and Price here.
▪Below the labels, conditions can be entered. You can enter either single values or comparisons like ">42" or "<=10".
▪If multiple conditions have been entered in the same row, the database contents have to match the conditions in column 1 and column 2 and column 3, etc.
▪If multiple rows with conditions have been entered, the database contents have to match the conditions in row 1 or row 2 or row 3, etc.
Utilizing database functions
After having filled out database area and criteria area, you are ready to apply database functions.
Some examples, assuming that the following spreadsheet has been created:
In this spreadsheet, for example, the following calculations can be performed:
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. Same as above, except that the column was specified by its relative number instead of its label.