PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
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.
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.
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
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.
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 |
All the conditions declared in a ROW represent AND whereas all the conditions in the separate rows represent the OR CONDITIONS.
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.
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.
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.
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%.
SOLUTION:
The steps are already given in EXAMPLE 3.
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 2 which is correct.
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE