This week we are going to revisit what is typically referred to as an “Advanced Excel” function. The word “Advanced” can make it sound a bit scary, but it is, in fact, really quite Easy To Use. Database Functions are particularly useful in creating Interactive worksheets. The more commonly used of these special functions are DSUM, DAVERAGE, and DCOUNT.
A good place to start is 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. DCOUNT counts the cells that contain numbers in a database that match conditions that you specify, and has the following syntax, (Note that if Field is omitted, DCOUNT counts all records in the database that match the criteria):
DCOUNT(Database, Field, Criteria)
Let’s say that you have a database of 5,000 records and 4 Columns (or “Fields”), and your 4 columns have the following headers in cells A1:D1
Month, SalesPerson, Product, SalesValue
Refering to the Syntax, 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 Smith which were greater than $1,000. Here is what you do:
Create a Criteria block in cells F1:I2 like the following illustration:
Bamm! That is all there is to it! Try it out sometime, and you will be amazed at the new Muscle it will give you in Excel. And not only that, it's Easy!
No comments:
Post a Comment