PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

Contents

EXCEL DATABASE FUNCTIONS-DCOUNT

INTRODUCTION

EXCEL provides a complete set of functions to deal with the database data.

DATABASE data has the first row as headers.

DCOUNT FUNCTION GIVES THE COUNT OF THE NUMBERS IN A FIELD OR COMPLETE DATABASE IF THE GIVEN CONDITION IS FULFILLED.

DCOUNT function is very useful if we need to find out any number of items in a database or large data on the basis of different conditions.

PURPOSE OF DCOUNT FUNCTION IN EXCEL

DCOUNT FUNCTION COUNTS THE NUMBERS IN A GIVEN DATABASE ON THE BASIS OF GIVEN CONDITIONS. ONLY THE FIGURES WHICH SATISFY THE CONDITIONS ARE COUNTED.

PREREQUISITES TO LEARN DCOUNT

THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.

  • Basic understanding of how to use a formula or function.
  • If you already know how to use the database functions, it’ll be an edge.
  •  Of course, Excel software.
Helpful links for the prerequisites mentioned above

SYNTAX: DCOUNT FUNCTION

The Syntax for the DCOUNT function is

=DCOUNT(database or range, column or field, Criteria )

The DCOUNT function syntax has the following arguments:

  • Database    Database is the data on which we want to use the DCOUNT function. The data is specifically arranged in DATABASE STYLE with LABELS in the top row and data in the lower rows. The range mentioning the database is given including the cells containing the Labels.

  • Field   Column or field is the column on which the criteria will be applied. The column is given by the column name in the “” or it can be given as the column number of the database such as 1 ,2 and so on.

  • Criteria    It is the range which contains the criteria or conditions. It is important that the range contains the column label. It’ll become clearer with the example discussed below.

EXAMPLE:DCOUNT FUNCTION IN EXCEL

DATA SAMPLE

DCOUNT FUNCTION usage can be a bit confusing for the new learners. For the simplicity let us take an example to understand the Do’s and Don’t of DCOUNT.
We have a simple data of a school with number of students in different classes and their attendance.
The table below shows the data present with us.Let us try to find out a few analytical points from the table.
CLASSSTUDENTSATTENDANCE
I4797
II4765
III4071
IV4999
V5091
VI4978
VII4755
VIII4863
IX4988
X4774
XI4054
XII4656
XIII4255
XIV4963
1. Find out the NUMBER OF CLASSES WITH STUDENTS MORE THAN 40.
2. Find out the NUMBER OF CLASSES WITH ATTENDANCE MORE THAN 85%.
 
 
how to use dcount database function in excel
EXCEL: DCOUNT FUNCTION EXAMPLE

1. FINDING OUT THE NUMBER OF CLASSES WITH STUDENTS MORE THAN 40 MARKS

Let us try to find out the average number of students in every class.
Follow the steps to find out number of classes with more than 40 Students
  • After we have our data ready i.e. The table must be having the column labels at the top. [ There is no specific requirements for the column labels. The function would consider the first row as column labels. ]
  • The criteria needs to be declared as a range. The range must contain at least one column label at the top followed by the condition under the columns.
  • Our DATABASE range is given in the cells E6:G20.
  • Criteria is given in the cells I6 to K7.
  • THERE IS A TRICKY PROBLEM IN THIS CASE .WE WANT TO TAKE THE OUTPUT FROM THE CLASS COLUMN BUT AS IT IS CLEAR THAT CLASS COLUMN DOESN’T CONTAIN THE NUMBER. DCOUNT WILL FAIL IF APPLIED ON THE COLUMN NOT CONTAINING THE NUMBER. TO COMEOUT FROM THIS PROBLEM, WE’LL TAKE THE COLUMN STUDENTS, WHICH WILL WORK FOR US IN THE SAME WAY.
  • Put the formula in a cell where you want the result =DCOUNT(E6:G20,”STUDENTS”,I6:K7)
  • The function returns the result as 12.
  • The following animated picture shows the process of using DCOUNT function in Excel.
The explanation follows the picture below.
 
how to use dcount database function in excel
STEPS TO FIND THE NUMBER OF CLASSES WITH MORE THAN 40 STUDENTS USING DCOUNT

EXPLANATION: FINDING OUT THE NUMBER OF CLASSES WITH STUDENTS MORE THAN 40 MARKS

The function used for the solution is =DCOUNT(E6:G20,”STUDENTS”,I6:K7)

Let us analyze the formula used.

The first parameter i.e. E6:G20 is the complete range of the Database [data ].

The second parameter “STUDENTS” is the field or column on which the condition would work and result will be returned. As we want to know the Number of Classes with Students more than 40 we can take CLASS column as well as “STUDENT” column. But we don’t have CLASS column in the Arabic Numbers [1,2,3…] so we used the STUDENT column.

The column is always given in the Double Quotes. [” “].

The last parameter is the range containing the Criteria I6:k7.

Have a look at the way criteria is described. The column names must be same as the database so that the function can lookup and give you the result.

In this case , we mentioned >40 under the STUDENTS column so that we get the desired result.

 

2. FINDING OUT THE NUMBER OF CLASSES WITH ATTENDANCE MORE THAN 85%

Let us try to find out the average number of students in every class.
Follow the steps to find out number of classes with attendance more than 85%
  • After we have our data ready i.e. The table must be having the column labels at the top. [ There is no specific requirements for the column labels. The function would consider the first row as column labels. ]
  • The criteria needs to be declared as a range. The range must contain at least one column label at the top followed by the condition under the columns.
  • Our DATABASE range is given in the cells E6:G20.
  • Criteria is given in the cells I6 to K7.
  • THERE IS A TRICKY PROBLEM IN THIS CASE TOO .WE WANT TO TAKE THE OUTPUT FROM THE CLASS COLUMN BUT AS IT IS CLEAR THAT CLASS COLUMN DOESN’T CONTAIN THE NUMBER. DCOUNT WILL FAIL IF APPLIED ON THE COLUMN NOT CONTAINING THE NUMBER. TO COMEOUT FROM THIS PROBLEM, WE’LL TAKE THE COLUMN “ATTENDANCE”, WHICH WILL WORK FOR US IN THE SAME WAY.
  • Put the formula in a cell where you want the result =DCOUNT(E6:G20,”ATTENDANCE”,I12:K13)
  • The function returns the result as 4.
  • The following animated picture shows the process of using DCOUNT function in Excel.
The explanation follows the picture below.
how to use dcount database function in excel
STEPS TO FIND NUMBER OF CLASSES WITH MORE THAN 85% ATTENDANCE USING DCOUNT FUNCTION

EXPLANATION: FINDING OUT THE NUMBER OF CLASSES WITH ATTENDANCE MORE THAN 85%

The function used for the solution is =DCOUNT(E6:G20,”ATTENDANCE”,I12:K13)

Let us analyze the formula used.

The first parameter i.e. E6:G20 is the complete range of the Database [data ].

The second parameter “ATTENDANCE” is the field or column on which the condition would work and result will be returned. As we want to know the Number of classes with attendance more than 85%, we can take CLASS column as well as ATTENDANCE column. But we don’t have CLASS column in the Arabic Numbers [1,2,3…] so we used the ATTENDANCE column.

The column is always given in the Double Quotes. [” “].

The last parameter is the range containing the Criteria I12:k13.

Have a look at the way criteria is described. The column names must be same as the database so that the function can lookup and give you the result.

In this case , we mentioned >85 under the ATTENDANCE column so that we get the desired result.

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

[email protected]

KINDLY SPEND FEW SECONDS HERE....

-We might be compensated for the visits or purchases you make by clicking the ads on this website .  They help us to keep bringing excellent quality articles for you.
-Click on the pictures if you can’t read. All of them are High Resolution .
-If you can’t find what you were looking for, kindly message in the chat box.
*CLICK ANYWHERE ELSE TO CLOSE THIS WINDOW