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

EXCEL DATABASE FUNCTIONS-DCOUNTA

INTRODUCTION

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

DATABASE data has the first row as headers.

DCOUNTA FUNCTION GIVES THE COUNT OF THE NON EMPTY CELLS IN A GIVEN DATABASE WHICH SATISFIES THE GIVEN CONDITION.

DCOUNTA function is very useful if we simply want to know the size of the database or data.

PURPOSE OF DCOUNTA FUNCTION IN EXCEL

DCOUNTA FUNCTION COUNTS THE NON BLANK CELLS IN THE GIVEN DATABASE ON THE BASIS OF GIVEN CONDITIONS. ONLY THE DATA WHICH SATISFY THE CONDITIONS ARE COUNTED.

PREREQUISITES TO LEARN DCOUNTA

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

What Excel does? How to use formula in Excel?

SYNTAX: DCOUNTA FUNCTION

The Syntax for the DCOUNTA function is

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

The DCOUNTA function syntax has the following arguments:

Database Database is the data on which we want to use the DCOUNTA 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:DCOUNTA FUNCTION IN EXCEL

DATA SAMPLE

DCOUNTA FUNCTION usage can be a bit confusing for the new learners.

Let us take an example to learn the examples of DCOUNTA FUNCTION in EXCEL.

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.

CLASS	STUDENTS	ATTENDANCE
I	48	78
II	46	50
III	47	89
IV	43	74
V	41	76
VI	46	53
VII	41	73
VIII	45	87
IX	42	78
X	43	73
XI	43	83
XII	41	88
XIII	44	68
XIV	49	80

To demonstrate the use of DCOUNTA FUNCTION in Excel let us try to :

1. Find out the NUMBER OF CLASSES WITH STUDENTS MORE THAN 45 with more than 90% attendance .

2. Find out the NUMBER OF CLASSES out of class I, V, XI, XIV having the attendance more than 85%.

EXAMPLE 1. FIND OUT THE NUMBER OF CLASSES WITH STUDENTS MORE THAN 45 WITH MORE THAN 90% ATTENDANCE.

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 45 Students with attendance more than 90%.

Once the table is ready, we can start our procedure. [ 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. [The criteria needs to be declared in the sheet itself rather than passing it directly]
Our DATABASE range is given in the cells E6:G20.
Criteria is given in the cells I6 to K7.
The Criteria is shown in the picture below.
Notice the way the criteria is defined.

All the conditions declared in a ROW represent AND whereas all the conditions in the separate rows represent the OR CONDITIONS.

The following picture shows the criteria for finding out the number of classes with more than 45 students with attendance more than 90 percent.

Put the formula in a cell where you want the result =DCOUNTA(E6:G20,”STUDENTS”,I6:K7) [=DCOUNTA(E6:G20,”CLASS”,I6:K7 would work equally well in this case].
The function returns the result as 0. [ As no class fulfills our criteria which comes out to be very stringent. ]
The following picture shows

The explanation follows the picture below.

EXAMPLE 1: EXPLANATION

The function used for the solution is =DCOUNTA(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. We can use CLASS column too in this field.

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 students as >45 and attendance >90 percent.

As the conditions got too stringent , let us try another set of conditions and check out the result.

Let us try to find out the number of classes with >40 students and >75 percentage.

EXAMPLE 2. FIND OUT THE NUMBER OF CLASSES WITH STUDENTS MORE THAN 40 WITH MORE THAN 75% ATTENDANCE.

In the previous example , we used the conditions which resulted in the output 0. So let us ease the conditions a bit and find out the number of classes with the students more than 40 and attendance more than 75%.

SOLUTION:

The steps are already given in EXAMPLE 1.

The data is same and the function is same too.

We will just change the criteria in the CRITERIA RANGE.

The following animated picture shows the process.

The result comes out to be 8 which is correct.

EXAMPLE 3. FINDING OUT THE NUMBER OF CLASSES OUT OF CLASS I, V, XI, XIV, 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 out of class I, V, XI, XIV 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.
The criteria is shown below.
The criteria contains different conditions. As already discussed, the conditions in a single row represent the AND CONDITION whereas the conditions in different rows represent the OR CONDITION. As we want to know the classes which have more than 85 pc attendance, we specified the attendance as >85 in all the four rows separately. For the classes we made use of =”=text” format for the comparison.

Put the formula in a cell where you want the result =DCOUNTA(E6:G20,”CLASS”,I12:K16)
The function returns the result as 0 as no condition is satisfied.

The final result is shown by the picture below.

EXPLANATION: EXAMPLE 3

The function used for the solution is =DCOUNTA(E6:G20,”CLASS”,I12:K16)

Let us analyze the formula used.

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

The second parameter is the FIELD NAME which is given as CLASS from which we want the result.

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

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

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 against all the conditions specifying the CLASS column so that we get the desired result.

EXAMPLE 4: FINDING OUT THE NUMBER OF CLASSES OUT OF CLASS I, V, XI, XIV, WITH ATTENDANCE MORE THAN 78%

In the previous example , we used the conditions which resulted in the output 0. So let us ease the conditions a bit and find out the number of classes from the given options CLASS I, V , XI, XIV having attendance more than 78%.