The Excel Dproduct function

The Excel Dproduct function

 calculates the product of a field (column) in a database for selected records, that satisfy user-specified criteria.
The syntax of the function is :

DPRODUCT( database, field, criteria )

where the arguments are shown in the table below:

database	-	A range of cells containing the database. The top row of the database should specify the field names.
field	-	The field (column) within the database, that you want to calculate the product of. This can either be a field number, or can be the field name (ie. the header in the top row of the database) encased in quotes (eg. "Date", "Type", etc)
criteria	-	A range of cells that contain the criteria, to specify which records should be included in the calculation. 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. eg. Name Date Tom >01/01/2011

for further information see: http://www.exceltraining-london.co.uk/excelFunctions/ExcelLondon/excelfunctiondictionary.html

Wildcards
You can also use the following wildcards in text-related criteria:
?    -    matches any single character
*    -    matches any sequence of characters
if you do actually want to find the ? or * character, type the ~ symbol before this character in your search.
eg. the condition "A*e" will match all cells containing a text string beginning with "A" and ending in "e".

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/2011, FALSE)
or
	-	a text string (eg. "Text", "Monday")
or
	-	an expression (eg. ">12", "<>0")

Note that the Excel database functions are not case sensitive. So, for example, the criteria ="Tuesday" will be satisfied by cells containing the text "Tuesday" or "tuesday".

Excel Dproduct Function Examples

	A	B	C	D
1	Name	Date	Test	Score
2	Gary	01-Jan-2011	Test1	4
3	Gary	01-Jan-2011	Test2	4
4	Gary	01-Jan-2011	Test3	3
5	Gary	05-Jan-2011	Test1	3
6	Gary	05-Jan-2011	Test2	4
7	Gary	05-Jan-2011	Test3	3
8	Kev	02-Jan-2011	Test1	2
9	Kev	02-Jan-2011	Test2	3
10	Kev	02-Jan-2011	Test3	5
11	Kev	05-Jan-2011	Test1	3
12	Kev	05-Jan-2011	Test2	2
13	Kev	05-Jan-2011	Test3	5

The following examples are based on the simple database on the right, which stores the ratings, across three tests, taken by two men on different dates.

Example 1

In the example below, the Dproduct function is used to calculate the product of Gary's scores on all three tests on 05-Jan-2010. The criteria are specified in cells F1 - G2 and the Dproduct formula is shown in cell F3.

	F	G
1	Name	Date
2	Gary	05-Jan-2011
3	=DPRODUCT( A1:D13, "Score", F1:G2 )

The above Dproduct function calculates the product of the scores in cells D5, D6 & D7, and therefore returns the value 36.