DCOUNT function
DCOUNT counts how many rows that meet your criteria hold a number in the column you choose. It reads its criteria from a block of cells with matching headers.
Example
Read it as a sentence: in the table Q1:S6, count the cells with numbers in the Qty column for the rows that match the criteria in U1:U2 (Item is Apple), which gives 2.
Excel shows 2
Q
R
S
U
1
Item
Region
Qty
Item
2
Apple
North
10
Apple
3
Pear
South
20
4
Apple
South
30
5
Fig
North
40
6
Kiwi
East
Data
Syntax
DCOUNT(database, field, criteria)
-
database(required) - Range (with header row) that holds the list.
-
field(required) - Column to use, given as its header text in quotes or as a column number.
-
criteria(required) - Range with matching headers on top and conditions underneath.
Returns: Count of numeric cells in matching rows.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=DCOUNT(Data!Q1:S6,"Qty",Data!U1:U2) | 2 | Criteria Item=Apple (2 rows) |
=DCOUNT(Data!Q1:S6,"Qty",Data!V1:V2) | 1 | Criteria Item=Pear (1 row) |
=DCOUNT(Data!Q1:S6,"Qty",Data!W1:W2) | 0 | No matching rows |
=DCOUNT(Data!Q1:S6,"Qty",Data!X1:Y2) | 1 | AND criteria (same row) |
=DCOUNT(Data!Q1:S6,"Qty",Data!Z1:Z3) | 3 | OR criteria (two rows) |
=DCOUNT(Data!Q1:S6,"Qty",Data!AA1:AA2) | 3 | Numeric criteria >15 |
=DCOUNT(Data!Q1:S6,0,Data!U1:U2) | #VALUE! | Field number 0 |
=DCOUNT(Data!Q1:S6,4,Data!U1:U2) | #VALUE! | Field number beyond width |
The Data sheet these formulas read
A cell that starts with = holds that formula.
Q
R
S
U
V
W
X
Y
Z
AA
1
Item
Region
Qty
Item
Item
Item
Item
Region
Item
Qty
2
Apple
North
10
Apple
Pear
Plum
Apple
North
Apple
>15
3
Pear
South
20
Pear
4
Apple
South
30
5
Fig
North
40
6
Kiwi
East
Data
Watch out for
- Criteria headers must match the database headers exactly.
- Excel refuses the formula outright if you type an array constant such as {1,2,3} where a range is expected; point it at cells instead (measured in Excel 16.0.20527).
Where it works
- Excel
- DCOUNT is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use. Sheets has it, but takes cell ranges; array-constant databases are Excel-only.
Part of Database functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .