Related Function:

The DCOUNT function returns the number of cells containing numbers, in a field (column) of a database  that matches conditions you that specify.

  • This function is very similar to the COUNTIFS function, which was introduced as a new function in Excel 2007.

Syntax

=DCOUNT(database,[field],criteria)

Arguments

Argument Description
database The range of cells that makes up the list or database. The top row of the database should specify the field names
[field] Optional. Indicates which column is used in the function

  This can either be a field number, or can be the field name, i.e. the header in the top row of the database, encased in quotes, e.g. “Gender”, “Subject”, etc
  If the [field] argument is omitted, the DCOUNT function simply returns the count of all records that satisfy the supplied criteria
criteria The range of cells that contains the conditions that you specify. 

  The range can include one or more criteria, which are presented as a field name in one cell and the condition for that field in the cell below, e.g.

Gender Age
Male >10

Note: The criteria supplied beneath each field heading can be either:

  • a numeric value (including an integer, decimal, date, time, or logical value) (eg. 10, 01/01/2010, FALSE), or
  • a text string (eg. “Name”, “Monday”), or
  • an expression (eg. “>8”, “<>0”)

Note: The Excel database functions are not case sensitive. So, the criteria =”Male” will be satisfied by cells containing the text “Male” or “male”.

Examples

  A B C D E F G H I
1 Name Gender Age Subject Score   Subject Gender  
2 Amy Female 8 Math 63%   Science Male  
3 Amy Female 8 English 78%        
4 Amy Female 8 Science 39%   Subject Gender  
5 Bill Male 8 Math 55%   Math Female  
6 Bill Male English 71%        
7 Bill Male  8 Science pending   Subject Score  
8 Sue Female 9 Math     English >60%  
9 Sue Female English 52%        
10 Sue Female Science 48%        
11 Tom Male Math 78%        
12 Tom Male English 69%        
13 Tom Male Science 65%        
14                  
15 Formula   Result  
16 =DCOUNT(A1:E13,”Score”,G1:H2) 1 Finds that there are 2 rows for which the Gender is “Male” and the Subject is “Science”, however, E7 is not a number, the function only counts E2
17 =DCOUNT(A1:E13,”Score”,G4:H5) 1 Finds two rows in which the Subject is “Math” and the Gender is “Female”, however, cell E8 is blank, the functions only counts cell E2
18 =DCOUNT(A1:E13, ,G7:H8) 3 Finds three rows for which the Subject is “English” and the Score is greater than 60%

Note: You can also use the following wildcards in text-related criteria arguments:

      ?  –  matches any single character
  *  –  matches any sequence of characters

eg. the condition “A*e” will match any substring beginning with “A” and ending in “e”

If you do actually want to find the ? or * character, type the ~ symbol before this character in your search.