7. D is for Database function
“Excel’s Database functions are some of the most powerful and underutilized functions in Excel,” Zelman said. Those formulas starting with “D” such as DAVERAGE, DCOUNTA, DVAR, and so on, are all set up to work on a database, which is typically shown as a flat table of numbers and text (rows and columns).
The Database functions all use the same syntax: DCOUNTA(Database, field, criteria). He specified four steps for setting up a Database function.
- Ensure the raw data has column headings.
- Add a few blank rows (“the sandbox”) at the top of the raw data.
- Copy the column headings to the top row in the sandbox, and in the rows underneath, specify the criteria.
- Stacking the criteria means they are joined by an “OR”. Having criteria in the same row means they are joined by an “AND”. In the snippet shown here, there are two types of records being pulled from the database: gold medals won by Norway OR silver medals won by Canada.
8. “Exact” string matching versus “includes”
An example problem used DGET to extract from the database a single record that matched specified string criteria. Zelman cautioned us that having a search string criterion of GDP without quotes would also collect records of GDPA, GDPAAA, and so on.
In order to carry out an exact string search, the cell must contain =”=GDP”. You can read more about exact string matching in the Advanced Filter here.
On the flip side, if you want a string that merely contains GDP, use asterisks before and after the string, *GDP*, as the criterion. That will obtain records for AGDP, GDPA, and AGDPA. ª