How to Use DCOUNT Function in Excel (5 Suitable Examples)

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.

dcount function in excel

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.


Download Practice Workbook

Download this practice workbook.


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

=DCOUNT(database, field, criteria)

⏺ 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 fulfill the requirement. After that, it takes the value into count.

Take 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:

How to Use DCOUNT Function in Excel

As 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 better understanding:

dcount function in excel

Here, you can see we wanted to count how many data have Age greater than 25 and also 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.


5 Practical Examples of DCOUNT Function in Excel

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 equals to particular criteria.

Remember, the DCOUNT function is not case-sensitive. If you Search for “BALL”, it will also take “Ball” or “ball” into count.

To demonstrate this, we are using the following dataset:

Count Matching Elements Using DCOUNT Function

Here, we have a dataset of some salespersons. Now, our goal is to count the number of data contains the 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:

Count Matching Elements Using DCOUNT Function

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:

Use DCOUNT Function for Greater Equal Values

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:

Use DCOUNT Function for Not Equal Values in Excel

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:

Use DCOUNT Function for Not Equal Values in Excel

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:

Dcount to Find the Number of Values that Begins with Particular Characters in Excel

Here, Max and Madission start with the characters “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:

DCOUNT Function for Dates

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 ranges 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.


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.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!


Related Article

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo