How to use the DCOUNT function
What is the DCOUNT function?
The DCOUNT function counts cells containing only numbers and that meet a condition or criteria.
Table of Contents
1. Introduction
When is the count of a collection useful?
The count of observations is really useful if you want to find out:
- How many invoices exists in a particular month.
- How many students are above a specific grade level.
- How many observations in a study meet the 95% confidence interval.
- How many products are smaller than 80 inches?
- How many reports are between two specific years?
The count of observations, or the number of data points, is also an essential component in various mathematical formulas and statistical calculations. Here are a few examples:
- Arithmetic Mean (Average): Formula: Mean = (Sum of all values) / (Number of values)
The count of observations is needed to divide the sum of all values by the total number of values to calculate the arithmetic mean or average. - Variance and Standard Deviation:
Variance Formula: σ² = Σ(x - μ)² / (N - 1)
Standard Deviation Formula: σ = √(Σ(x - μ)² / (N - 1))
N represents the count or number of observations. The count is used in the denominator to calculate an unbiased estimate of the population variance and standard deviation for a sample. - Correlation Coefficient: Formula: r = Σ[(x - x̄)(y - ȳ)] / √[Σ(x - x̄)² * Σ(y - ȳ)²]
The count of observations is needed to calculate the means (x̄ and ȳ) and the summations involved in the correlation coefficient formula. - Linear Regression: Formula: y = a + bx
Where a = ȳ - b * x̄, and b = Σ[(x - x̄)(y - ȳ)] / Σ(x - x̄)²
The count of observations is required to calculate the means (x̄ and ȳ) and the summations involved in determining the slope (b) and y-intercept (a) of the linear regression line. - Probability Calculations: In probability theory, the count of outcomes or events is often needed to calculate probabilities, such as the probability of drawing a specific card from a deck or rolling a particular number on a die.
Does the DCOUNT function count blank cells, boolean, and text values?
No, blank cells, boolean values and text values are not counted.
Does the DCOUNT function ignore error values?
Yes, it ignores error values.
What is DCOUNT an abbreviation of?
DCOUNT is an abbreviation of Database Count.
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.
Why use the DCOUNT function?
The DCOUNT function counts cells containing only numbers that match a condition or criteria in a list/database whereas the COUNT function counts cells containing only numbers without a condition/criteria.
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.
2. Syntax and Arguments
DCOUNT(database, field, criteria)
| database | Required. The cell reference to a list or database. |
| field | Required. 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. |
| 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. |
4. Example 1
This example shows how to count numbers in cell range D7:D12 if they are larger than 250 and smaller than 700. The criteria is specified in cell B3 and C3, note that both has column header "Number" in order to filter correctly.
The data in cell range B6:D12:
| Item | Size | Number |
| A102 | M | 370 |
| A103 | L | 690 |
| A099 | S | 310 |
| A412 | S | 190 |
| A341 | L | 550 |
| A340 | M | 730 |
The condition is in cell range B2:C3:
| Number | Number |
| >250 | <700 |
The arguments are:
database = B6:D12
field = 3
criteria = B2:C3
The first argument is the database which in this case is in cell range B6:D12, the second argument is a column number corresponding to columns in cell range B6:D12 from left to right. The third argument is the a cell reference to the condition or critera.
Formula in cell D15:
The formula counts cells in column 3 (D) that contains a number and is less than 700 and larger than 250 which are cells D7, D8, D9, and D11. They all contain numbers and the DCOUNT function returns 4. Cells D10 and D12 are not counted because they are larger than or equal to 750 or smaller than or equal to 250.
5. Example 2
This example demonstrates how to use the asterisk character to filter rows if the corresponding cell on the same row in B6:B11 contains 3. The asterisk matches any character(s) from 1 to many.
The data in cell range B6:D12:
| Item | Size | Number |
| A102 | M | 370 |
| A103 | L | 690 |
| A099 | S | 310 |
| A412 | S | 190 |
| A341 | L | 550 |
| A340 | M | 730 |
The condition is in cell range B2:C3:
| Item |
| *3* |
The arguments are:
database = B6:D12
field = 3
criteria = B2:C3
Cells B7,B10, and B11 contain number 3, the corresponding cells in D6:D11 all contain numbers. The DCOUNT function returns 3.
Formula in cell D14:
The first argument is a cell reference pointing to the database or the list, in this case, cell range B5:D11. The second argument is a number representing a given column in B5:D11 from left to right. For example, 3 is the third column in cell range B5:D11 which is D5:D11.
The third argument is the location of the criteria and this example has only one condition.
6. Example 3
This example demonstrates three different criteria:
- If values in column "Item" begin with A and then 1 or more characters.
- If values in column "Size" are not equal to "S". The smaller than and larger than characters combined results in "not equal to".
- If values in column "Number" are larger than 600.
All these criteria are specified on the same row which means that all criteria must match or in other words "AND" logic is performed.
The data in cell range B6:D12:
| Item | Size | Number |
| A102 | M | 370 |
| A103 | L | 690 |
| A099 | S | 310 |
| B412 | S | 190 |
| A341 | L | 550 |
| A340 | M | #DIV/0! |
The conditions are in cell range B2:C3:
| Item | Size | Number |
| A* | <>S | >600 |
The arguments are:
database = B6:D12
field = 3
criteria = B2:C3
Formula in cell D14:
The first column "Item" has four matching values in cells B6,B7,B8,B10, and B11. The second column named "Size" has four matching values in cells C6,C7,C10, and C11.
The last column "Number" has one value larger than 600 in cell D7. This means that only D7 is valid based on criteria, D7 is also a number meaning that the DCOUNT function returns 1.
Functions in 'Database' category
The DCOUNT 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