Md. Abdul Kader
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 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.
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.
Read More: How to Use DCOUNT Function in Excel
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
Read More: How to Use Database Functions in Excel
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.
Read More: How to Use Excel DSUM Function
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.
Read More: How to Use DMAX Function in Excel with Multiple Criteria
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.
Read More: How to Use DMIN Function in Excel
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.
