- View Offline
- Multiple key sorting
- Single and multiple column numeric filters
- Creating a top-ten list with values or percentages
- Setting up subtotals
- Creating multiple-field criteria filters
- Creating unique lists from repeating field data
- Using the Remove Duplicates command
- Finding duplicate data with specialized arrays
- Counting the number of unique items in a list
- Using SUMIF and COUNTIF functions
- Working with the database functions such as DSUM and DMAX
Skill Level Appropriate for all
In Excel there is a category of functions called database functions, and although less frequently used in the past because of the SUMIF family of functions has been expanded greatly, these database functions rely on the kinds of criteria ranges used with the Advanced Filter. If you haven't used the Advanced Filter these might seem a little strange to you. But there are some advantages here and there. And these functions do mesh well with those Advanced Filter applications, particularly when you've got unusual calculations SUMIF and COUNTIF can't handle.
Now for reference in Column J I've simply listed here the various database functions. If you remove the letter D from each of these of course you probably recognize most of those anyway. Now here is a strange thing. In Excel 2007 and in Excel 2010 if you're a little bit curious about these database functions, you heard a little bit about them, if you go to the Formulas tab you say, "Well, I don't see them here, they must be here under More Functions." But we're not seeing them; they're not shown here.
Maybe that's some indication as to what Microsoft plans to do with these functions in the future. But we don't see them. However, if you click the fx button on the left side of the Formula Bar and look at the categories that are available here you will see Database functions, and here are those 12 functions. And what they all have in common is that they refer to a criteria range as the third argument. And if you have worked with the Advanced Filter you probably recognize on the screen here that potentially this is a criteria range. This is or maybe the two of them together are.
So, let's talk about how we might use one of these, for example DAVERAGE, =daverage. We'd like to find the average salary of our full time people. Now I think a lot of you know possibly you could be using one of the SUMIF, COUNTIF, AVERAGEIF functions here. Let's choose this function as an example of how we might use the others. We're looking at a database. I'm simply going to drag across columns A through F. In other situations you might be highlighting all the cells. Make sure you do include the title row there. Comma. Which field are we trying to tabulate data from? From left to right it's the seventh column, Column G. So we put in the number 7 here, comma, and how about this criteria range? And here your knowledge of using Advanced Filter would come into play.
We simply want to highlight the cells J1 and J2. What's the average salary of our or full time people, that's what we are calculating right now, and there it is. If we wanted to know the total salary of those salaries that are greater than 80,000 or maybe more interestingly a count of how many salaries are over 80,000, perhaps we'd use DCOUNT, =dcount. So once again like in the previous example we're looking at this database here. Comma.
We're tabulating a data from Column G so that's the seventh column, comma, and our criteria range this time around is these cells here. Tell me how many cells have salaries greater than 80,000, and there are 71 of them. So perhaps you can begin to see how this has some merit. Again they seem to be on the wane in terms of being widely used in Excel worksheets, but there's some real power and capability.
And if you do work with the Advanced Filter concept you're aware of how these have some real potential here and there are quite a few variations on how you use the Advanced Filter. And these functions do mesh nicely with that capability.
Sign up for a Premium Membership to download courses for Internet-free viewing.
Watch offline with your iOS, Android, or desktop app.Start Your Free Trial
After signing up, download the course here or from the iOS/Android App.