This article describes the formula syntax and usage of the DCOUNTA function in Microsoft Excel.
Description
Counts the nonblank cells in a field (column) of records in a list or database that match conditions that you specify.
The field argument is optional. If field is omitted, DCOUNTA counts all records in the database that match the criteria.
Syntax
DCOUNTA(database, field, criteria)
The DCOUNTA function syntax has the following arguments:
-
Database Required. The range of cells that makes up the list or database. A database is 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.
-
Field Optional. Indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as "Age" or "Yield," or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
-
Criteria Required. The range of cells that contains the conditions that you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.
Remarks
-
You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label for specifying the condition.
For example, if the range G1:G2 contains the column label Income in G1 and the amount $10,000 in G2, you could define the range as MatchIncome and use that name as the criteria argument in the database functions.
-
Although the criteria range can be located anywhere on the worksheet, do not place the criteria range below the list. If you add more information to the list, the new information is added to the first row below the list. If the row below the list is not blank, Excel cannot add the new information.
-
Make sure that the criteria range does not overlap the list.
-
To perform an operation on an entire column in a database, enter a blank line below the column labels in the criteria range.
Examples
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data. If you copy any of the following samples into Excel, be sure you select all the cells in this table, including the one in the uppermost left corner.
Tree | Height | Age | Yield | Profit | Height |
|---|---|---|---|---|---|
| ="=Apple" | >10 | <16 | |||
| ="=Pear" | |||||
| Tree | Height | Age | Yield | Profit | |
| Apple | 18 | 20 | 14 | 105.0 | |
| Pear | 12 | 12 | 10 | 96.0 | |
| Cherry | 13 | 14 | 9 | 105.0 | |
| Apple | 14 | 15 | 10 | 75.0 | |
| Pear | 9 | 8 | 8 | 76.8 | |
| Apple | 8 | 9 | 6 | 45.0 | |
| Formula | Description | Result | |||
| =DCOUNTA(A4:E10, "Profit", A1:F2) | Counts the rows (1) containing "Apple" in column A with a height >10 and <16. Only row 8 satisfies these three conditions. | 1 |
Criteria examples
-
When you enter =text in a cell, Excel interprets that as a formula and tries to calculate it. To enter =text so Excel doesn't try to calculate it, use this syntax:
=''= entry ''
Where entry is the text or value you want to find. For example:
What you type in the cell | What Excel evaluates and displays |
|---|---|
| ="=Davolio" | =Davolio |
| ="=3000" | =3000 |
-
When you filter text data, Excel doesn't distinguish between uppercase and lowercase characters. However, you can use a formula to perform a case-sensitive search.
The following sections provide examples of complex criteria.
Multiple criteria in one column
Boolean logic: (Salesperson = "Davolio" OR Salesperson = "Buchanan")
To find rows that meet multiple criteria for one column, type the criteria directly below each other in separate rows of the criteria range.
In the following data range (A6:C10), the criteria range (B1:B3) is used to count the rows that contain either "Davolio" or "Buchanan" in the Salesperson column.
Salesperson | ||
|---|---|---|
| ="=Davolio" | ||
| ="=Buchanan" | ||
| Category | Salesperson | Sales |
| Beverages | Suyama | $5,122 |
| Meat | Davolio | $450 |
| produce | Buchanan | $6,328 |
| Produce | Davolio | $6,544 |
| Formula | Description | Result |
| '=DCOUNTA(A6:C10,2,B1:B3) | Counts the number of rows (3) in A6:C10 that meet either of the "Salesperson" conditions in rows 2 and 3. | =DCOUNTA(A6:C10,2,B1:B3) |
Multiple criteria in multiple columns where all criteria must be true
Boolean logic: (Type = "Produce" AND Sales > 2000)
To find rows that meet multiple criteria in multiple columns, type all of the criteria in the same row of the criteria range.
In the following data range (A6:C12), the criteria range (A1:C2) is used to count the rows that contain "Produce" in the Category column and a value greater than $2,000 in the Sales column.
Category | Salesperson | Sales |
|---|---|---|
| ="=Produce" | >2000 | |
| Category | Salesperson | Sales |
| Beverages | Suyama | $5,122 |
| Meat | Davolio | $450 |
| Produce | Buchanan | $935 |
| Produce | Davolio | $6,544 |
| Beverages | Buchanan | $3,677 |
| Produce | Davolio | $3,186 |
| Formula | Description | Result |
| '=DCOUNTA(A6:C12,,A1:C2) | Counts the number of rows (2) in A6:C12 that meet the conditions in row 2 (="Produce" and >2000). | =DCOUNTA(A6:C12,,A1:C2) |
Multiple criteria in multiple columns where any criteria can be true
Boolean logic: (Type = "Produce" OR Salesperson = "Davolio")
To find rows that meet multiple criteria in multiple columns, where any criteria can be true, type the criteria in different rows of the criteria range.
In the following data range (A6:C10), the criteria range (A1:B3) displays all rows that contain "Produce" in the Type column or "Davolio"
Category | Salesperson | |
|---|---|---|
| ="=Produce" | ||
| ="=Davolio" | ||
| Category | Salesperson | Sales |
| Beverages | Suyama | $5,122 |
| Meat | Davolio | $675 |
| produce | Buchanan | $937 |
| Produce | Buchanan | |
| Formula | Description | Result |
| '=DCOUNTA(A6:C10,"Sales",A1:B3) | Counts the number of rows (2) in A6:C10 that meet either of the conditions in A1:C3, where the "Sales" field is not empty. | =DCOUNTA(A6:C10,"Sales",A1:B3) |
Multiple sets of criteria where each set includes criteria for multiple columns
Boolean logic: ( (Salesperson = "Davolio" AND Sales >3000) OR (Salesperson = "Buchanan" AND Sales > 1500) )
To find rows that meet multiple sets of criteria, where each set includes criteria for multiple columns, type each set of criteria in separate rows.
In the following data range (A6:C10), the criteria range (B1:C3) is used to count the rows that contain both "Davolio" in the Salesperson column and a value greater than $3,000 in the Sales column, or the rows that contain both "Buchanan" in the Salesperson and a value greater than $1,500 in the Sales column.
Category | Salesperson | Sales |
|---|---|---|
| ="=Davolio" | >3000 | |
| ="=Buchanan" | >1500 | |
| Category | Salesperson | Sales |
| Beverages | Suyama | $5,122 |
| Meat | Davolio | $450 |
| produce | Buchanan | $6,328 |
| Produce | Davolio | $6,544 |
| Formula | Description | Result |
| '=DCOUNTA(A6:C10,,B1:C3) | Counts the number of rows (2) in A6:C10 that meet all conditions in B1:C3. | =DCOUNTA(A6:C10,,B1:C3) |
Multiple sets of criteria where each set includes criteria for one column
Boolean logic: ( (Sales > 6000 AND Sales < 6500 ) OR (Sales < 500) )
To find rows that meet multiple sets of criteria, where each set includes criteria for one column, include multiple columns with the same column heading.
In the following data range (A6:C10), the criteria range (C1:D3) is used to count rows that contain values between $6,000 and $6,500 and values less than $500 in the Sales column.
Category | Salesperson | Sales | Sales |
|---|---|---|---|
| >6000 | <6500 | ||
| <500 | |||
| Category | Salesperson | Sales | |
| Beverages | Suyama | $5,122 | |
| Meat | Davolio | $450 | |
| produce | Buchanan | $6,328 | |
| Produce | Davolio | $6,544 | |
| Formula | Description | Result | |
| '=DCOUNTA(A6:C10,,C1:D3) | Counts the number of rows (2) that meet the conditions in row 2 (>6000 and <6500) or meet the condition in row 3 (<500). | =DCOUNTA(A6:C10,,C1:D3) |
Criteria to find text values that share some characters but not others
To find text values that share some characters but not others, do one or more of the following:
-
Type one or more characters without an equal sign (=) to find rows with a text value in a column that begin with those characters. For example, if you type the text Dav as a criterion, Excel finds "Davolio," "David," and "Davis."
-
Use a wildcard character.
The following wildcard characters can be used as comparison criteria.
Use | To find |
|---|---|
| ? (question mark) | Any single character |
| * (asterisk) | Any number of characters |
| ~ (tilde) followed by ?, *, or ~ | A question mark, asterisk, or tilde |
In the following data range (A6:C10), the criteria range (A1:B3) is used to count rows with "Me" as the first characters in the Type column or rows with the second character equal to "u" in the Salesperson column.
Category | Salesperson | Sales |
|---|---|---|
| Me | ||
| ?u* | ||
| Category | Salesperson | Sales |
| Beverages | Suyama | $5,122 |
| Meat | Davolio | $450 |
| produce | Buchanan | $6,328 |
| Produce | Davolio | $6,544 |
| Formula | Description | Result |
| '=DCOUNTA(A6:C10,,A1:B3) | Counts the number of rows (3) that meet either of the conditions in A1:B3. | =DCOUNTA(A6:C10,,A1:B3) |
Criteria created as the result of a formula
You can use a calculated value that is the result of a formula as your criterion. Remember the following important points:
-
The formula must evaluate to TRUE or FALSE.
-
Because you are using a formula, enter the formula as you normally would, and do not type the expression in the following way:
=''= entry ''
-
Do not use a column label for criteria labels; either keep the criteria labels blank or use a label that is not a column label in the range (in the examples below, Calculated Average and Exact Match).
If you use a column label in the formula instead of a relative cell reference or a range name, Excel displays an error value, such as #NAME? or #VALUE!, in the cell that contains the criterion. You can ignore this error because it does not affect how the range is filtered.
-
The formula that you use for criteria must use a relative reference to refer to the corresponding cell in the first row.
-
All other references in the formula must be absolute references.
Filtering for values greater than the average of all values in the data range
In the following data range (A6:C10), the criteria range (C1:C2) is used to count rows that have a value in the Sales column greater than the average of all the Sales values (C7:C10). The average is calculated in cell C4, and the result is combined in cell C2 with the formula =">"&C4 to create the criteria that is used.
Sales | ||
|---|---|---|
| =CONCATENATE(">",C4) | ||
| Calculated Average | ||
| =AVERAGE(C7:C10) | ||
| Category | Salesperson | Sales |
| Beverages | Suyama | $5,122 |
| Meat | Davolio | $450 |
| produce | Buchanan | $6,328 |
| Produce | Davolio | $6,544 |
| Formula | Description | Result |
| '=DCOUNTA(A6:C10,,C1:C2) | Counts the number of rows (3) that meet the condition (>4611) in C1:C2. The condition in C2 is created by concatenating =">" with cell C4, which is the calculated average of C7:C10. | =DCOUNTA(A6:C10,,C1:C2) |
