How to use the DCOUNTA function
What is the DCOUNTA function?
The DCOUNTA function counts nonempty cells in a column you specify, in a database where records also meet a condition or criteria.
Table of Contents
1. Introduction
What is DCOUNTA an abbreviation of?
DCOUNTA is an abbreviation of Database Counta. COUNTA probably means count all, in other words, it counts all cells in a given range that contains data.
What is a database in this context?
Excel defines a database as a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.
Where can you place the criteria range?
You can place your criteria range wherever you want on your worksheet, however, it is not recommended below the list/database. The function needs a blank row below the list to work properly.
What criteria characters are allowed?
Allowed criteria range characters are less than and greater than signs <>, use them to specify a criteria range. Also, asterisks * can be used to match partial strings.
Does the DCOUNTA function ignore blank cells?
Yes, blank cells are ignored.
Does the DCOUNTA function ignore error values?
No, the DMAX function does not ignores error values. A cell containing an error value is counted.
What is the difference between the DCOUNTA function and the COUNTA function?
The DCOUNTA function counts non empty cells based on a condition or criteria in a list/database whereas the COUNTA function counts non empty cells without using a condition/criteria.
How to include the entire database in the calculation?
To include the entire list/database enter a blank line below the criteria range column labels. In other words, don't specify any criteria.
When is the count of non-empty cells useful?
- Data Validation: Working with large datasets it's essential to ensure that all required fields are filled in. It allows you to identify any missing data or incomplete entries.
- Conditional Calculations: Perform a calculation only if a certain number of cells in a range are filled in, or skip a calculation if a particular column is empty.
- Inventory Management: Count the number of items in stock or the number of products with non-empty quantities. This information can be used for stock monitoring, reordering, or generating reports.
- Survey Analysis: Counting the number of respondents who provided an answer to a particular question. This can help identify questions with low response rates or highlight popular choices in multiple-choice questions.
- Project Planning: Count the number of tasks or activities that have been assigned resources or start/end dates. This information can be valuable for tracking project progress, resource allocation, or identifying potential bottlenecks.
2. Syntax and Arguments
DCOUNT(database, field, criteria)
| database | Required. The cell reference to a list or database. |
| field | Optional. The field argument lets you choose which column to use. You can use the column name enclosed with double quotation marks or the corresponding column number. If not entered DCOUNTA counts all records in the database that match the condition. |
| criteria | Required. A cell reference to the criteria range. The criteria range needs to have column labels and a at least one condition below the column label. |
3. Example 1
In an inventory database, you want to count the number of products where the "Size" category column is equal to "L", do not count empty cells in column "Number". How can you use the DCOUNTA function to get this count?
The data in cell range B6:D12, note that cell D11 is empty.
| Item | Size | Number |
| A102 | M | 370 |
| A103 | L | 690 |
| A099 | S | 310 |
| A412 | S | 190 |
| A341 | L | |
| A340 | M | 730 |
The condition is in cell range B2:B3:
| Size |
| L |
The arguments are:
database = B6:D12
field = 3
criteria = B2:B3
Formula in cell B15:
The formula finds one cell that meets the condition "L" and the corresponding cell in column "Number" is non-empty.
4. Example 2
In a sales database, you want to count the number of orders where the "Product Category" column is not blank, the "Order Status" column is set to "Shipped" and the "Number" column is set to numbers larger than 200. How can you use the DCOUNTA function to get this count?
This example demonstrates AND logic, two different conditions must match on the same row.
The data in cell range B6:D12, note that cell B9 is empty.
| Product | Order Status | Number |
| A102 | Shipped | 370 |
| A103 | Not shipped | 690 |
| Shipped | 310 | |
| A412 | Shipped | 190 |
| A341 | Not shipped | 550 |
| A340 | Not shipped | 730 |
The criteria are in cell range B2:C3:
| Order Status | Number |
| Shipped | >200 |
The arguments are:
database = B6:D12
field = 3
criteria = B2:C3
Formula in cell B15:
The formula matches the condition "Shipped" in cells C7:C9 and C10, the condition above 200 matches cells D7:D12 except D10. If all conditions are taken into consideration cells B7 and B9 are evaluated, however, cell B9 is empty. The count is 1.
5. Example 3
This example demonstrates OR logic, two different conditions must match on the same row.
You want to count the number of products, in a price database, where the "Item" column is not blank, the "Number" column is smaller than 250 and larger than 680. How can you use the DCOUNTA function to get this count?
The data in cell range B6:D12, note that cell D12 is empty.
| Item | Size | Number |
| A102 | M | 370 |
| A103 | L | 690 |
| A099 | S | 310 |
| A412 | S | 190 |
| A341 | L | 550 |
| M | 730 |
The criteria are in cell range B2:C4. The criteria are on a row each, this makes the formula perform or logic instead of AND logic.
| Number | Number |
| <250 | |
| >680 |
The arguments are:
database = B6:D12
field = 3
criteria = B2:C4
Formula in cell B15:
The formula counts records where the number is above 700 or below 250. Also, the cell must not be blank in column 1 (Item). Only row 8 and row 10 match the criteria, the function returns 2 in cell B15.
Functions in 'Database' category
The DOUNTA function function is one of 11 functions in the 'Database' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form