When we need to count the number of non-empty cells in a given database, the Excel **DCOUNTA** function is the perfect function for the job. In this article, we’ll discuss this function and how to use it.

The above figure shows an overview of the utilization of the **DCOUNTA** function.

## Introduction to the DCOUNTA Function

**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 for the given criteria.

**Function Objective**

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

**Syntax**

`DCOUNTA (database, [field], criteria)`

**Arguments Explanation **

Argument | Required/Optional | Explanation |
---|---|---|

database |
Required | The range of cells that contain the database or list |

field |
Optional | 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 can be included in the criteria. Some instances are provided in the table below:

Criteria | Type | Behavior |
---|---|---|

Monitor |
String | Match ‘Monitor’ or ‘monitor’ |

Mo* | Wildcard | Match the row that starts with ‘Mo’ |

*or | Wildcard | Match the row that ends with ‘or’ |

*nit* | Wildcard | Match 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**

The count of the number of matched non-blank cells in the specified list or database.

## DCOUNTA Function in Excel: 5 Different Cases

In the following dataset, some items are provided with their price, quantity (Qty), and sales in different states of the US. We’ll use this dataset to demonstrate uses of the **DCOUNTA** function.

### Example 1 – Using the DCOUNTA Function with a Single Criteria

Suppose we want to count the number of cells that contain ‘TV’. We can use the following formula;

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

**Formula Breakdown**

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

**➤ B4:B5** is the criteria.

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

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

### Example 2 – Using the DCOUNTA Function with Multiple Criteria Rows

For criteria which cover multiple rows, we can use the below formula:

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

**Formula Breakdown**

➤ **B8:F16** denotes the database.

➤ **5** determines 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:

The number of sales that matches the criteria is 3.

### Example 3 – Using the DCOUNTA Function with Multiple Criteria in a Single Column

To determine the number of cells that contain ‘TV’ and ‘AC’ (multiple criteria in a single column), we can apply the following formula:

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

**Formula Breakdown**

➤ **B8:F16** denotes the database.

➤ **5 **is the value of the ‘Sales’ field.

➤ **B4:B6** refers to the criteria, where two items i.e. ‘TV’ and ‘AC’ are listed.

The number of cells that contain ‘TV’ and ‘AC’ is 5.

### Example 4 – Using the DCOUNTA Function with Multiple Criteria in Multiple Columns

In the previous example, we used the **DCOUNTA **function for multiple criteria in a single column. But the function can also be applied with multiple criteria in multiple columns.

#### 4.1 – Multiple Criteria in Multiple Columns Using AND Logic

**AND** logic returns the output as **TRUE** if all conditions are matched. To count the number of cells when the price of ‘TV’ is greater than $250, we can utilize the following formula:

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

**Formula Breakdown**

➤ **B8:F16** refers to the database.

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

Only one cell matches the criteria i.e. ‘TV’ with a price greater than $250.

#### 4.2 – Multiple Criteria in Multiple Columns Using OR Logic

**OR** logic returns the output as **TRUE **if any condition is matched. To find the number of cells which match ‘TV’ or ‘Utah’, we can apply the following formula:

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

**Formula Breakdown**

➤ **B8:F16** denotes the database

➤ **5** determines the number of matched cells

➤ **B4:C5** refers to criteria, that specify the number of cells containing ‘TV’ or ‘Utah’

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

### Example 5 – Using the DCOUNTA Function with Multiple Sets of Criteria

Lastly, to find the number of cells that match multiple sets of criteria in multiple columns, we can use 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 the criteria, when the price is greater than $200 but less than $300 and the price is greater than $100.

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

## Things to Remember

- The
**DCOUNTA**function considers both numeric and string values, whereas the**COUNTA**function considers only the numeric values. - If the criteria are left blank, the
**DCOUNTA**function returns**#VALUE!**error.

**Download Practice Workbook**

**<< Go Back to Excel Functions ****|**** Learn Excel**