# 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Â

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.

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

`=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.

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.

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

âž¤ 5Â is the value of the ‘Sales’ field

âž¤ B4:B6 refers to criteria where two items i.e. â€˜TVâ€™ and â€˜ACâ€™ are available.

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.

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â€™

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 the database.

âž¤ 5Â 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.

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.

<< Go Back to Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!