Tuesday, February 1, 2011
Database Functions = Excel Muscle!
Database Functions are extraordinarily powerful and come in different types. Among the more commonly used of these special functions are DSUM, DAVERAGE, and DCOUNT. In order to gently break into this subject, we are going to look at the DCOUNT database function, since once you know how to use this, the others will be simple additions to your Excel Arsenal.
Let’s say that you have a database of 5,000 records and four Columns (or “Fields” as they are more appropriately called). Your four columns have the following headers in cells A1:D1
Month, SalesPerson, Product, SalesValue
DCOUNT counts the cells that contain numbers in a database that match conditions that you specify, and has the following syntax, (Note that if a field is omitted, DCOUNT counts all records in the database that match the criteria):
DCOUNT(Database, Field, Criteria)
Database is your entire range, with the first row of the database containing labels for each column.
Field refers to which column is used in the function. Enter the column label enclosed between double quotation marks, such as "SalesPerson" or "SalesValue," or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so forth.
Criteria is the range of cells that contains the conditions that you specify. You can use any range for the criteria argument, as long as the argument includes a column label.
Now, let’s say that you want to count all sales for John Smith which were greater than $1,000. Here is what you do:
Create a Criteria block in cells F1:I2 like the following illustration:
=DCOUNT(A1:D5001, “SalesValue”, F1:I2)
Bam! Your answer is given. Seriously, try this out; you will be amazed at the new Muscle it will give you in Excel!