Related Function:

The DPRODUCT function multiplies the values in a field (column) of records in a list or database that match conditions that you specify.

Syntax

=DPRODUCT(database,field,criteria)

Arguments

Argument Description
database The range of cells that makes up the list or database
field Indicates which column is used in the function

  The field 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. “Area”, “Quarter”, etc
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, 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”.

Examples

  A B C D E F G H I
1 Name Gender Age Subject Score   Subject Gender  
2 Amy Female 8 Math 3   Science Male  
3 Amy Female 8 English 4        
4 Amy Female 8 Science 1   Subject Age  
5 Bill Male 8 Math 2   Math >8  
6 Bill Male English 3        
7 Bill Male  8 Science 3        
8 Sue Female 9 Math 2        
9 Sue Female English 2        
10 Sue Female Science 2        
11 Tom Male Math 4        
12 Tom Male English 3        
13 Tom Male Science 3        
14                  
15 Formula   Result  
16 =DPRODUCT(A1:E13,”Score”,G1:H2) 9 The product of science test scores of male students, i.e. E7 * E13 or 3 *3 = 9
17 =DCOUNT(A1:E13,”Score”,G4:H5) 8 The product of math test scores of students greater than age 8, i.e. E8 * E11 or 2 * 4 = 8

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.