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.

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.

Introduction to the DCOUNTA Function

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

Function Objective

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

Syntax

DCOUNTA (database, [field], criteria)

Arguments Explanation 

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

Return Value

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

DCOUNTA Function in Excel: 5 Different Cases

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

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.

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

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.

The output will look like this.

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.

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.

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.

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.

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.

Download Practice Workbook

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.

<< Go Back to Excel Functions | Learn Excel