The DCOUNT function is a built-in function in Excel that is categorized as a Database Function. The DCOUNT function of Microsoft Excel is one of the essential functions. We use this in a lot of scenarios. Besides, you can perform various calculations with this. For that reason, it will save you a lot of time. In this tutorial, you will learn every detail of the DCOUNT functions in Excel. This tutorial will be on point with suitable examples and proper illustrations.
The above screenshot is an overview of the article which represents a simplified application of the DCOUNT function in Excel. You’ll learn more about the dataset as well as the methods and functions under different criteria in the following sections of this article.
Introduction to DCOUNT Function
⏺ Function Objective
Counts the cells that contain numbers in a field (column) of records in a list or database that fit the requirements that we define.
⏺ Syntax
⏺ Arguments Explanations
Argument
Required/Optional
Explanations
database
Required
The Database or the range of cells where you want to perform the operation. A database is a list of corresponding data in which rows of connected details are records, and columns of data are fields. The first row of the list includes labels for each column.
field
Required
The column name or the column number points excel which column in the database to calculate. It will be from the database only. It may be a field number / Index number. You have to type the column tag retained between double quotation marks, such as “Marks” or “Name,” or a digit (without quotation marks) that defines the position of the column within the list. For example, 1 for the first column, 2 for the second column, and so on.
criteria
Required
The range of cells that contain the conditions you define. The criteria must include at least one field name. You can put multiple ranges of cells in this Criteria field.
⏺ Returns
It returns a numeric value.
⏺ Available in
Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000.
How to Use DCOUNT Function in Excel
Now, the Excel DCOUNT function counts the matching values or records based on the criteria we specify. When you set a requirement from a dataset, it searches for the data that fulfills the requirement. After that, it takes the value into count.
Take a look at the following dataset:
Here, we have a dataset of some people. You can see we have Names, Ages, and Salaries. Now, in our criteria, we have set Age to “>25”. It means we want to count the entries that have Ages greater than 25.
Now, the formula we are using in Cell C12:
=DCOUNT(B4:D9,"Age",B11:B12)Then, it will show the following result:
We have three data that have Ages greater than 25. Now, you can also count the cells based on multiple criteria.
Take a look at the following dataset for a better understanding:
Here, you can see we wanted to count how many data have an Age greater than 25 and also a Salary greater than $3000. Clearly, you can see, there are two data in the dataset.
In the end, you can use the DCOUNT function in Excel on different datasets according to your criteria.
DCOUNT Function in Excel: 5 Practical Examples
The DCOUNT function is one of the Database Functions. In the following section, we are providing you with five suitable and effective examples of DCOUNT functions that you can implement into your dataset. Make sure you check them all. We recommend you learn and apply all these. It will surely enrich your Excel knowledge.
1. Count Matching Elements Using DCOUNT Function
Now, you can use the DCOUNT function for matching elements. Or you can say, count the number of values that equal to particular criterion.
To demonstrate this, we are using the following dataset:
Here, we have a dataset of some salespersons. Now, our goal is to count the number of data contained by Salesperson John and Product TV.
Now, type the following formula in Cell D13 and press Enter.
=DCOUNT(B4:D10,"Sales",B12:C13)After that, you will see the following result:
As you can see, we have successfully used the DCOUNT function in Excel.
2. Use DCOUNT Function for Values that are Greater or Equal
Now, you can use the DCOUNT function of Excel to calculate the number of occurrences for a greater equal value. You can use multiple criteria of greater equal values.
Take a look at the following screenshot:
Here, we have a dataset of students and their marks. Now, we are going to find the number of entries in the dataset that have an ID greater than 1005 and Marks greater equal to 80.
Now, type the following formula in Cell D13 and press Enter.
=DCOUNT(B4:D10,"ID",B12:C13)After that, you will see the following result:
Here, we have two entries that fulfill our criteria. So, as you can see, we are successful in using the DCOUNT function in Excel.
3. Use DCOUNT Function for Values that are Not Equal
Now, similar to the previous method, you can also use the DCOUNT to count the cells that are not equal to your criteria. The DCOUNT function will check the dataset and count how many values match the condition.
Take a look at the following dataset:
Here, we want to count the data that is not equal to the product TV.
Now, type the following formula in Cell C13 and press Enter.
=DCOUNT(B4:D10,"Sales",B12:B13)After that, you will see the following result:
Here, you can see, there are three instances that don’t contain the product TV. In this way, you can implement the DCOUNT function into your worksheet.
4. Find the Number of Values that Begins with Particular Characters in Excel
Now, there may be a situation where you need to count the number of instances that begin with a specific word. In such cases, you can also use the DCOUNT function to measure these. For this, you have to use the wildcard characters in Excel to perform.
Take a look at the following screenshot:
Here, our goal is to count the data that begins with the word “Ma”.
Now, type the following formula in Cell C13 and press Enter.
=DCOUNT(B4:D10,"Age",B12:B13)After that, you will see the following result:
Here, Max and Madisson start with the character “Ma”. As you can see, we have successfully used the DCOUNT function in Excel.
5. Use of DCOUNT Function for Dates
If you have read all the examples that we have shown earlier, you can easily do this. You can work with the date also with the DCOUNT function in Excel. You can use the equal to, greater than, not equal to, etc. procedures with dates.
Take a look at the following dataset:
Here, we want to count the instances of the persons who were born before 2/23/1990.
Now, type the following formula in Cell C13 and press Enter.
=DCOUNT(B4:D10,"Salary",B12:B13)You can use “Birth Year” instead of “Salary”. Both will give you the same result.
After that, you will see the following result:
As you can see, three persons were born before 2/23/1990. Now, you can use the DCOUNT function in Excel to perform various operations with the dates.
💬 Things to Remember
✎ When criteria contain more than one row, it joins each row with OR logic, and the representations in a provided criteria row with AND logic.
✎ The DCOUNT function only measures numeric values in the provided field when conditions fit. You can use the DCOUNTA function to calculate either numbers or text.
✎ Your criteria range header must be similar to the database’s header.
✎ Be certain that the criteria range does not coincide with the list. Maintain at least a one-row gap in between them.
✎ DCOUNT will throw a #Name. Error if the field value is blank or text value.
✎ If the criteria in DCOUNT are empty, it will throw a #Value error.
Download Practice Workbook
Download this practice workbook.
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge to use the DCOUNT function in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Keep learning new methods and keep growing!
<< Go Back to Excel Functions | Learn Excel
