What it does – It is used to count numeric values in a data range satisfying a particular criteria
=DCOUNT(database, field, criteria)
database – It represents the range of data that is to be considered. The database should be properly structured, i.e., the first row of the database should contain column labels.
field – It represents the column which contains the values that are to be extracted or operated on. One can either use the column number or the column label enclosed within inverted commas as the field.
criteria – The range of cells that contain the user-specified conditions which need to be satisfied for the values to be included in the count.
=DCOUNT(A3:C18, "Leaves", G3:H4)
//This returns sum of values in “Leaves” column satisfying the G3:H4 criteria and lying in A3:C18 data range
Note – The DCOUNT function in Google Sheets can only be used to count numeric values. If used to count non-numeric values, the DCOUNT function will return 0 as its output.
Sample Google Sheets template with formula here.
Just like the DSUM function, the DCOUNT function in Google Sheets is a database function which allows the users to count numeric values from a dataset using an SQL-like query. It is ideally used when one needs to count the values that satisfy one or more user-defined conditions from an unsorted database.
Steps to use the DCOUNT function in Google Sheets
Let’s take up a task wherein we are required to count the number of employees in the Marketing department who have already taken more than 10 leaves. Assuming that we have the following dataset, we follow the steps below to use the DCOUNT function in Google Sheets and complete our task.
Step 1: Format the dataset such that it has proper column labels. The column labels chosen are as follows:
- Column 1: Names
- Column 2: Department
- Column 3: Leaves
Once formatted, we have the following dataset:
Step 2: Input the criteria specified by the user in empty cells in a table-like manner with the same headings as your formatted data. For our task, we mention the following criteria:
- Department: Marketing
- Leaves: >10
Step 3: In the cell, where we want the output to be present, we enter the following formula and press enter:
=DCOUNT(A3:C18, "Leaves", G3:H4)
The function above counts the number of times it finds an employee that belongs to the Marketing department and has taken more than 10 leaves already.
Thus, through the use of the DCOUNT function in Google Sheets, in only three steps, we were able to find the total number of employees who have taken a leave of more than 10 days from the Marketing Department.
Case #1: DCOUNT function used to count non-numeric values
As mentioned previously if we were to use the DCOUNT function in Google Sheets to calculate non-numeric values, we would have 0 as the output.
For instance, if we use the same dataset, only this time we calculate the total number of employees working in the Marketing department we will not have the right output.
To calculate the total members in the marketing department we use the following formula:
=DCOUNT(A3:C18, “Department”, G3:G4)
On pressing enter, we receive the following output:
Since the “Field” in the function pointed to a column containing text inputs, the DCOUNT function resulted in an output of 0. To count non-text values, one can use the DCOUNTA function that accepts the same parameters as the DCOUNT function in Google Sheets.
Case #2: DCOUNT function used with improper column labels
The column label in the criteria and the field must be an exact match. In case they are different, the DCOUNT function produces 0 as an output.
For instance, if the column label of the argument, Field, was “Leave” and not “Leaves”, we would have been the #VALUE error as shown below:
To know more about the error messages that pop out in Google Sheets, click here.
The different database functions allow the users to perform different operations on data which is properly formatted through an SQL-like query. It is the perfect formula to use to maximize efficiency and not waste time on small, less important daily tasks.
To know more about functions that will improve your efficiency in Google Sheets, head to our blog.
Frequently Asked Questions (FAQs)
1. What is the difference between the DCOUNT and the DCOUNTA function?
While the DCOUNT function can be used to count only the numeric values, the DCOUNTA function can be used to count both numeric and non-numeric values. Thus, it’s better to use the DCOUNTA function when presented with a choice between the two functions.
2. When not to use the DCOUNT function in Google Sheets?
It’s futile to use the DCOUNT function when you re trying to count the non-empty cells in a range or when you are trying to count the number of unique items in a range.
Have a look at the following articles that will help you maximize efficiency when you work with data.
How to use the DSUM function in Google Sheets
How to use the DGET function in Google Sheets