Good Databases (Part 2)

Last week, we looked at what constitutes best practices in creating a Good Database. Now we are going to examine how to best Extract Information from your data.


First order of business is to Name Your Ranges. An easy trick to use is to select your entire database (including the names of the ranges in the header) and go to Define Names and choose Create Names from Selection. Put a check next to Top Row and click OK – Voila, you have just named all of your ranges!

Now is when the fun begins! (This may sound a bit complex at first, but the concept is actually very simple). What we will be working with is called the SUMPRODUCT function.

SUMPRODUCT is an amazingly versatile function which can be used in many different ways to extract information from databases. You use its simple logic of “Yes or No” (0 or 1) to multiply corresponding components in user defined arrays, and return the sum of those products. The format is expressed as =SUMPRODUCT((array1)*(array2)* ...).

So enough talk! Let’s actually use it and see what it can do. Duplicate the database below, (or use one of your own), and name the ranges. Then, (using the example database), in any cell outside the database (if you wish, it can be on a separate worksheet in the same workbook) enter =SUMPRODUCT((Office = “West”)*(Sales)). Please note the use of Double Parentheses and Quotation Marks around West.

So, what is the SUMPRODUCT formula doing in this case? It is going through your entire database (it would work just as well with a database of 10,000+ records) and each time the first parameter of (Office = “West”) is true it assigns the number 1 and each time it is false it assigns the number 0. Then it multiplies times the Sales figure and Adds them all up!

Please hear me when I say this: If you are into Excel, please give this a try. I can guarantee you that you will be a Total Rock Star in your company!

Next week: One last, really cool example (a bit more complex) of using SUMPRODUCT with a database. See you then!