Excel provides a very simple way to pull out data from databases to perform desired functions over it. It also provides list of functions which can be used exclusively for databases, you just need to create a database or simple table and let Excel know to consider it as one complete database, define some criteria to pull out specified entries. Excel DCOUNT function lets you to count the occurrence of specified data if the criteria is met.
Launch Excel 2010 spreadsheet on which you need to apply database functions, for instance we have included a spreadsheet containing fields; Names, Course, and Marks on which we will be applying DCOUNT function.
For applying database functions, we need to create a new table having the criteria on which we will be evaluating functions. In the same spreadsheet create a table by the name of Criteria, as shown in the screenshot below. Make sure that the field labels in the this table must be same as in the other table.
Now we will be evaluating database DCOUNT function. To start off with, we will be creating a DCOUNT label beneath the table.
The DCOUNT function will yield the count of the students who met the specified criteria i.e >60. The syntax of DCOUNT function is;
The first argument in the table is database which refers to location of database table in the spreadsheet, next argument field refers to the column/field number in database table, and criteria refers to any criteria which need to be met, it can be either location of cells or statement defining criteria.
We will be writing this as;
A1:C10 in the table is location of the cells which makes up database, 3 is the number of the column in the database table (Marks) for which we have defined criteria, and E3:F4 is the location of Criteria table. On evaluating this function, it will yield 6, as there are 6 students in total who have secured marks greater than 60.
Now if you want to check that in how many courses, specific student secured marks greater than 60 then you just need to enter the name of the student in the Criteria table, It will pull-out all the corresponding data for the specified name and yield result 2, As you can see in the screenshot below there are only two courses in which specified student has secured marks greater than 60.