Database Functions = Excel Muscle!

In our continued exploration of Excel this week, we are going to take a look at what is typically referred to as an “Advanced Excel” function. Don’t be concerned, however, because although it seems a bit complex at first, it is really quite easy to use.

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:

Then go to an open cell (e.g. F4) and put the following formula in place:

           =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!