Related Function:

The DCOUNTA function counts the nonblank cells in a field (column) of records in a list or database that match conditions that you specify.

Syntax

=DCOUNTA(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 function simply returns the count of all records that satisfy the supplied criteria
criteria A range of cells that contain the criteria, to specify which records should be included in the count

  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, e.g. 10, 01/01/2010, FALSE
  • a text string, e.g. “Name”, “Tuesday”
  • an expression, e.g. “>10”, “<>0”).

The Excel database functions are not case sensitive. So, for example, the criteria =”Math” will be satisfied by cells containing the text “Math” or “math”.

Note: The function is similar to the DCOUNT function, the difference being that the DCOUNTA function counts all non-blank cells where the DCOUNT function only counts cells containing numerical values.

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 Notes
16 =DCOUNTA(A1:E13,”Score”,G1:H2) 2 Function finds that there are 2 rows for which the Gender is “Male” and the Subject is “Science”. As the “Score” column for both of these rows is non-blank, the function returns the value 2
17 =DCOUNTA(A1:E13,”Score”,G4:H5) 1 Function finds two rows in which the Subject is “Math” and the Gender is “Female”. As the “Score” column for one of these rows is blank, the function returns the value 1
18 =DCOUNTA(A1:E13, ,G7:H8) 3 Function finds three rows for which the Subject is “English” and the Score is greater than 60%. As the “Score” column for both of these rows is non-blank, the function returns the value 3

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

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

e.g. 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.