Buzz
You have a large list of students and want to quickly analyze the total number of high-scoring students without spending much time. This article introduces you to the DCount function in Excel. It helps you use the DCount function with accompanying examples so you can address your requirements effectively.
Description of DCount Function
The DCount function performs a count of the number of values (numeric data) belonging to a field or column within a data range with specified conditions expressed in the criteria range.
DCount Function Syntax
DCOUNT(database, field, criteria)
Where:
- Database: Range or array containing the data to count, including headers in the data range, is a required parameter.
- Field: Name of the field or column used to check the counting condition, there are 2 ways to input field data:
+ Method 1: Directly enter the field name of the data column, make sure to enclose the column name in double quotes.
+ Method 2: Enter the corresponding ordinal number of that column counted from the left.
- Criteria: Data range used as conditions to count the quantity, this is a required parameter.
Attention to Dcount
- The criteria range (criteria parameter) must contain the field name.
- The criteria data range should not be placed below the list because if the area below the list is not empty, you cannot add information to it.
- Ensure that the criteria range does not overlap with the main data range.
Example of Using DCount Function
For the dataset of exam results of students participating in the selection test. Requirements:
- Count the total number of office students participating in the exam
- Count the total number of students with math scores greater than or equal to 8
- Count the total number of office students with math scores greater than or equal to 8 (counting with multiple conditions containing non-numeric data)
- Count the total number of office students with math, chemistry, and physics scores all greater than or equal to 8.
Instructions:
1. Count the total number of office students participating in the exam:
Count the total number of office students participating in the exam => condition range Class=VP. Enter the formula in the cell to count: =DCOUNT(C4:I13,F4,C15:C16)
Press Enter to get the result:
As you can see, there are 3 office students participating in the exam but the result returns as 0. Since the Dcount function only counts with numerical data, the condition Class = VP belongs to the character type, so the function returns a value of 0. Therefore, with this condition, you need to use another function to count.
So, for the first requirement, you need to understand that the Dcount function only counts with numerical data types.
2. Count the total number of students with math scores greater than or equal to 8
The data to count belongs to the numerical data type in the Math Score column. Enter the formula in the cell where you want to calculate the number of students with math scores greater than or equal to 8:
=DCOUNT(C4:I13,G4,D15:D16)
With:
- C4:I13: Represents the data range containing the data to be counted.
- G4: Name of the data column used to check the condition.
- D15:D16: Range containing the conditions.
Ensure that the field name of the range containing the conditions matches the field name in the source data table.
Press Enter to get the result:
3. Count the total number of office students with math scores greater than or equal to 8
Here, the counting condition contains 2 criteria, but the Dcount function only counts numerical data. You can perform the following method. To include non-numeric criteria in the condition range, use the column name containing numerical data for the criteria. Create a data range containing Class and Math Score as shown. Enter the formula in the cell where you want to calculate: =DCOUNT(C4:I13,G4,C15:D16)
Thus, the condition range contains 2 criteria, but you only include the column containing numerical data for the criteria:
Press Enter to get the result:
4. Count the total number of office students with math, chemistry, and physics scores all greater than or equal to 8
For this requirement, count numerical data with multiple criteria and include non-numeric criteria.
Set up the condition range as shown. Enter the formula in the cell where you want to calculate: =DCOUNT(C4:I13,G4,C15:F16)
Press Enter to get the result:
