How to Use DCOUNTA Function in Excel (5 Examples)

When you need to count the number of non-empty cells in a given database, you may utilize the Excel DCOUNTA function. In this article, I’ll exhibit the basics and uses of the function with the necessary explanations.

Overview of the DCOUNTA Function

The above figure shows the overview of the utilization of the DCOUNTA function. Right away,  I’ll show you the uses of the function elaborately.


Download Practice Workbook


Introduction to the DCOUNTA Function

The DCOUNTA is a Database function, introduced in Excel 2003 version, returns the number of non-blank cells with reference to a list or database on given criteria. Look at the following figure.

The DCOUNTA Function

Function Objective

Enumerates the number of non-empty cells in a given database based on criteria.

Syntax

DCOUNTA (database, [field], criteria)

Arguments Explanation 

Argument Required/Optional Explanation
database  Required The range of cells that construct the database or list
field Optional This argument specifies which column is utilized in the function.
criteria Required The range of cells where the conditions are specified

Note:
⇰ Criteria Examples: A variety of expressions might be included in the criteria. Some instances are provided in the table below.

Criteria Type Behavior

Monitor

String Meet ‘Monitor’ or ‘monitor’
Mo* Wildcard The row that starts with ‘Mo’
*or Wildcard The row that ends with ‘or’
*nit* Wildcard The row that contains ‘nit’ in any position
>$200 Comparison Greater than $200
<$100 Comparison Less than $100
>=$100 Comparison Greater than or equal to $100
<> Comparison Not Blank
>1/9/2022 Number Greater than Jan 9, 2022

Return Value

Count the number of matched non-blank cells in a list or database.


How to Use the DCOUNTA Function in Excel

Let’s be introduced with the following dataset where some items are provided with their price, quantity (Qty), and sales based on the states of the US.

Dataset


1. Using the DCOUNTA Function for Single Criteria

Assuming that you want to count the number of cells that contain ‘TV’. In such a situation you may utilize the following formula.

=DCOUNTA(B8:F16,,B4:B5)

Formula Breakdown

➤ B8:F16 is the range of cells (database)

➤ B4:B5 refers to criteria

Needless to say, the value of the ‘field’ argument is not necessary in this case.

Using The DCOUNTA Function for Single Criteria

The above picture clearly reveals that the DCOUNTA function only counts the number of cells that contain ‘TV’ (a string data type). And the output is 3.


2. Utilizing the DCOUNTA Function While Dealing Multiple Criteria Rows

If you want to consider the criteria which cover multiple rows, you may use the below formula.

=DCOUNTA(B8:F16,5,B4:C6)

Formula Breakdown

B8:F16 denotes database

5 is used for determining the number of matched cells in the case of sales

B4:C6 refers to criteria where two items i.e. ‘TV’ and ‘AC’ are considered and especially the price criteria which is greater than $200 in the case of ‘TV’ and greater than $250 in the case of ‘AC’.

The output will look like this.

Utilizing The DCOUNTA Function While Dealing Multiple Criteria Rows

Here, the number of sales that matches the criteria is 3


3. Applying the DCOUNTA Function Regarding Multiple Criteria in Single Column

Suppose, you want to determine the number of cells that contain ‘TV’ and ‘AC’ (multiple criteria in a single column), you may apply the following formula.

=DCOUNTA(B8:F16,5,B4:B6)

Formula Breakdown

B8:F16 denotes database

is the value of the ‘Sales’ field

B4:B6 refers to criteria where two items i.e. ‘TV’ and ‘AC’ are available.

Applying The DCOUNTA Function Regarding Multiple Criteria in Single Column

The above picture shows that the number of cells that contain ‘TV’ and ‘AC’ is 5.


4. Using the DCOUNTA Function for Multiple Criteria in Multiple Columns

In the previous example, we used the DCOUNTA function for multiple criteria in a single column. But if we need, we can utilize the function for multiple criteria in multiple columns.

4.1. Multiple Criteria in Multiple Columns Using AND Logic

Basically, the AND logic returns the output as TRUE if all conditions are matched. If you want to count the number of cells when the price of ‘TV’ is greater than $250, you may utilize the following formula.

=DCOUNTA(B8:F16,,B4:C5)

Formula Breakdown

B8:F16 refers to the database

B4:C5 refers to criteria that specify the number of cells of ‘TV’ when the price of ‘TV’ is greater than $250.

Multiple Criteria in Multiple Columns Using AND Logic

Here, we see that only one cell matches the criteria i.e. ‘TV’ and its price is greater than $250.


4.2. Multiple Criteria in Multiple Columns Using OR Logic

The OR logic mainly returns the output as TRUE if any condition is matched. If you want to find the number of cells which match ‘TV’ and ‘Utah’, you may apply the following formula.

=DCOUNTA(B8:F16,5,B4:C5)

Formula Breakdown

B8:F16 denotes database

5 is used for determining the number of matched cells in the case of sales

B4:C5 refers to criteria that specify the number of cells of ‘TV’ and ‘Utah’

Multiple Criteria in Multiple Columns Using OR Logic

Only two cells match the criteria i.e. the number of cells that contain ‘TV’ and ‘Utah’.


5. The DCOUNTA Function with Multiple Sets of Criteria 

Lastly, if your requirement is to find the number of cells that match multiple sets of criteria in multiple columns, you may follow the formula below.

=DCOUNTA(B8:F16,,B4:C6)

Formula Breakdown

B8:F16 is the range of cells for database

is the value of the ‘Sales’ field

B4:C6 refers to criteria when the price is greater than $200 but less than $300 and the price is greater than $100.

The DCOUNTA Function with Multiple Sets of Criteria

Here, the number of cells in the Sales column that match the criteria is 2.


Things to Remember

1. The DCOUNTA function considers both numeric and string values whereas the COUNTA function considers only the numeric values.

2. If the criteria are left blank, the DCOUNTA function returns #VALUE! error.


Conclusion

This is how we can apply the DCOUNTA function in Excel for various cases. I strongly believe that this article will articulate your Excel learning journey. However, if you have any queries or suggestions, please let me know in the comments section below.

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo