How to Use DCOUNTA Function in Excel (5 Examples)

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 

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

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;

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:

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:

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:

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:

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:

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