Excel DSUM Function

Microsoft Excel DSUM Function

Basic Description

The Excel Dsum function calculates the sum of a field (column) in a database for selected records, that satisfy user-specified criteria.

The function is very similar to the Excel Sumifs function, which was first introduced in Excel 2007.

The syntax of the Excel Dsum function is :

DSUM( database, field, criteria )

where the arguments are:

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 is to be summed. 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. "Area", "Quarter", 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. Quarter Area >2 South

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. "Name", "Saturday")
or
	-	an expression (eg. ">12", "<>0")

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

Excel Dsum Function Examples

	A	B	C	D
1	Quarter	Area	Sales Rep.	Sales
2	1	North	Jeff	$223,000
3	1	North	Chris	$125,000
4	1	South	Carol	$456,000
5	1	South	Tina	$289,000
6	2	North	Jeff	$322,000
7	2	North	Chris	$340,000
8	2	South	Carol	$198,000
9	2	South	Tina	$222,000
10	3	North	Jeff	$310,000
11	3	North	Chris	$250,000
12	3	South	Carol	$460,000
13	3	South	Tina	$395,000
14	4	North	Jeff	$261,000
15	4	North	Chris	$389,000
16	4	South	Carol	$305,000
17	4	South	Tina	$188,000

The following examples are based on the simple database on the right, which stores the sales figures for four sales representatives, working in the North or South area, over the four quarters of a year.

Example 1

In the example below, the Dsum function is used to calculate the total sales in quarters 3 & 4, in the "North" area. The criteria are specified in cells F1 - G2 and the Dsum formula is shown in cell F3.

	F	G
1	Quarter	Area
2	>2	North
3	=DSUM( A1:D17, "Sales", F1:G2 )

The above Dsum function calculates the sum of the values in cells D10, D11, D14 & D15, and therefore returns the value $1,210,000

Example 2

In the example below, the Dsum function is used to calculate the total sales in quarter 3, by sales reps with names beginning with the letter "C". Again, the criteria are specified in cells F1 - G2 and the Dsum formula is shown in cell F3.

	F	G
1	Quarter	Sales Rep.
2	3	C*
3	=DSUM( A1:D17, "Sales", F1:G2 )

The above Dsum function sums the values in cells D11 and D12 and so returns the value $710,000

Note that, in the above two examples, instead of typing in "Sales" for the Field argument, we could have simply used the number 4 (to denote the 4th column of the database).

Excel DSTDEV Function

Microsoft Excel DSTDEV Function

Basic Description

The Excel Dstdev function calculates the sample standard deviation of a field (column) in a database for selected records only. The records to be included in the calculation are defined by a set of one or more user-specified criteria.

The syntax of the function is :

DSTDEV( 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, for which the sample standard deviation is to be calculated. This can either be a field number, or it can be the field name (ie. the header in the top row of the database) encased in quotes (eg. "Subject", "Name", 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. Age Name >8 Amy

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. "Name", "Sunday")
or
	-	an expression (eg. ">8", "<>0")

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

Excel Dstdev Function Examples

	A	B	C	D	E
1	Name	Gender	Age	Subject	Score
2	Amy	Female	10	Math	63%
3	Amy	Female	10	English	78%
4	Amy	Female	10	Science	39%
5	Bill	Male	8	Math	55%
6	Bill	Male	8	English	71%
7	Bill	Male	8	Science	51%
8	Sam	Male	9	Math	39%
9	Sam	Male	9	English	52%
10	Sam	Male	9	Science	48%
11	Tom	Male	9	Math	78%
12	Tom	Male	9	English	69%
13	Tom	Male	9	Science	65%

The following examples are based on the simple database on the right, which stores the examination marks scored by four children in three different subjects.

Example 1

In the example below, the Dstdev function is used to find the sample standard deviation of scores obtained in English, by male students. The criteria are specified in cells G1 - H2 and the Dstdev formula is shown in cell G3.

	G	H
1	Subject	Gender
2	English	Male
3	=DSTDEV( A1:E13, "Score", G1:H2 )

The above Dstdev function calculates the sample standard deviation of the values in cells E6, E9 & E12, and therefore returns the value 10.4%.

Example 2

In the example below, the Dstdev function is used to find the sample standard deviation for the Math scores obtained by students over 8 years of age.

	G	H
1	Subject	Age
2	Math	>8
3	=DSTDEV( A1:E13, "Score", G1:H2 )

The above Dstdev function finds the sample standard deviation of the values in cells E2, E8 & E11 and so returns the value 19.7%

Note that, in the above two examples, instead of typing in "Score" for the field argument, we could have simply used the number 5 (to denote the 5th column of the database).

Excel DPRODUCT Function

Microsoft Excel DPRODUCT Function

Basic Description

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

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.

Example 2

In the example below, the Dproduct function is used to calculate the product of all scores for Test1 on dates prior to 05-Jan-2011.

	F	G
1	Test	Date
2	Test1	< 05-Jan-2011
3	=DPRODUCT( A1:D17, "Score", F1:G2 )

The above Dproduct function calculates the product of the scores in cells D2 and D8 and so returns the value 8.

Note that, in the above two examples, instead of typing in "Score" for the field argument, we could have simply used the number 4 (to denote the 4th column of the database).

Excel DMIN Function

Microsoft Excel DMIN Function

Basic Description

The Excel Dmin function finds the minimum value in a field (column) in a database for selected records only. The records to be included in the calculation are defined by a set of one or more user-specified criteria.

The syntax of the function is :

DMIN( 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 the minimum 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. "Area", "Quarter", 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. Quarter Area >1 North

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", "Sunday")
or
	-	an expression (eg. ">8", "<>0")

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

Excel Dmin Function Examples

	A	B	C	D	E
1	Name	Gender	Age	Subject	Score
2	Amy	Female	8	Math	63%
3	Amy	Female	8	English	78%
4	Amy	Female	8	Science	39%
5	Bill	Male	8	Math	55%
6	Bill	Male	8	English	71%
7	Bill	Male	8	Science	51%
8	Sue	Female	9	Math	39%
9	Sue	Female	9	English	52%
10	Sue	Female	9	Science	48%
11	Tom	Male	9	Math	78%
12	Tom	Male	9	English	69%
13	Tom	Male	9	Science	65%

The following examples are based on the simple database on the right, which stores the examination marks scored by four children in three different subjects.

Example 1

If, from the example database, we wanted to find the lowest score in Science, from students aged over 8, we could use the Dmin function as shown below.

In the example, the criteria are specified in cells G1 - H2 and the Dmin formula is shown in cell G3:

	G	H
1	Subject	Age
2	Science	>8
3	=DMIN( A1:E13, "Score", G1:H2 )

The above Dmin function calculates the minimum value in cells E10 & E13, and therefore returns the value 48%

Note that, in the above function, instead of typing in "Score" for the field argument, we could have simply used the number 5 (to denote the 5th column of the database).

Example 2

In the example below, the Dmin function is used to find the minimum Math examination score for a male child.

Again, the criteria are specified in cells G1 - H2 and the Dmin formula is shown in cell G3.

	F	G
1	Subject	Gender
2	Math	Male
3	=DMIN( A1:E13, "Sales", G1:H2 )

The above Dmin function finds the minimum of the values in cells E5 and E11 and so returns the value 55%

Excel DMAX Function

Microsoft Excel DMAX Function

Basic Description

The Excel Dmax function finds the maximum value in a field (column) in a database for selected records only. The records to be included in the calculation are defined by a set of one or more user-specified criteria.

The syntax of the function is :

DMAX( 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 the maximum 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. "Area", "Quarter", 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. Quarter Area >1 North

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. "North", "Friday")
or
	-	an expression (eg. ">5", "<>0")

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

Excel Dmax Function Examples

	A	B	C	D
1	Quarter	Area	Sales Rep.	Sales
2	1	North	Jeff	$223,000
3	1	North	Chris	$125,000
4	1	South	Carol	$456,000
5	1	South	Tina	$289,000
6	2	North	Jeff	$322,000
7	2	North	Chris	$340,000
8	2	South	Carol	$198,000
9	2	South	Tina	$222,000
10	3	North	Jeff	$310,000
11	3	North	Chris	$250,000
12	3	South	Carol	$460,000
13	3	South	Tina	$395,000
14	4	North	Jeff	$261,000
15	4	North	Chris	$389,000
16	4	South	Carol	$305,000
17	4	South	Tina	$188,000

The following examples are based on the simple database on the right, which stores the sales figures for four sales representatives, over the four quarters of a year.

Example 1

If, from the example database, we wanted to find the highest sales figure in the North area, during quarter 2, we could use the Dmax function as shown below.

In the example, the criteria are specified in cells F1 - G2 and the Dmax formula is shown in cell F3:

	F	G
1	Quarter	Area
2	2	North
3	=DMAX( A1:D17, "Sales", F1:G2 )

The above Dmax function calculates the maximum of the values in cells D6 & D7, and therefore returns the value $340,000

Note that, in the above function, instead of typing in "Sales" for the field argument, we could have simply used the number 4 (to denote the 4th column of the database).

Example 2

In the example below, the Dmax function is used to find the highest quarterly sales figure for sales rep "Carol", in quarters 2, 3 or 4.

Again, the criteria are specified in cells F1 - G2 and the Dmax formula is shown in cell F3:

	F	G
1	Sales Rep.	Quarter
2	Carol	>1
3	=DMAX( A1:D17, "Sales", F1:G2 )

The above Dmax function finds the maximum of the values in cells D8, D12 and D16, and so returns the value $460,000

Excel DGET Function

Microsoft Excel DGET Function

Basic Description

The Excel Dget function returns the value contained in a field (column) of a single record in a database.

The record is selected via a set of one or more user-specified criteria.

The syntax of the function is :

DGET( database, field, criteria )

where the arguments are shown in the following table:

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, from which the record is to be selected. 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. "Gender", "Subject", 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. Subject Age Math >8

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", "Thursday")
or
	-	an expression (eg. ">8", "<>0")

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

Excel Dget Function Examples

	A	B	C	D
1	Quarter	Area	Sales Rep.	Sales
2	1	North	Jeff	$223,000
3	1	North	Chris	$125,000
4	1	South	Carol	$456,000
5	1	South	Tina	$289,000
6	2	North	Jeff	$322,000
7	2	North	Chris	$340,000
8	2	South	Carol	$198,000
9	2	South	Tina	$222,000
10	3	North	Jeff	$310,000
11	3	North	Chris	$250,000
12	3	South	Carol	$460,000
13	3	South	Tina	$395,000
14	4	North	Jeff	$261,000
15	4	North	Chris	$389,000
16	4	South	Carol	$305,000
17	4	South	Tina	$188,000

The following examples use the simple database on the right, which stores the sales figures for four sales representatives, for each quarter of the sales year.

Example 1

In the example below, the Dget function is used to return the sales figure for Sales Rep. "Tina" in quarter 4. The criteria are specified in cells F1 - G2 and the Dget formula is shown in cell F3:

	F	G
1	Sales Rep.	Quarter
2	Tina	4
3	=DGET( A1:D17, "Sales", F1:G2 )

The above Dget function finds the record in row 17, and returns the value from the "Sales" column. Therefore, the function returns the value$188,000

Note that, in the above example, instead of typing in "Sales" for the Field argument, we could have simply used the number 4 (to denote the 4th column of the database).

Example 2

In the example below, we attempt to use the Dget function to extract the Sales figure for the South area in quarter 4.

	F	G
1	Area	Quarter
2	South	4
3	=DGET( A1:D17, "Sales", F1:G2 )

The criteria arguments for the above Dget function are satisfied by two rows of the database - rows 16 & 17. However, the Dget function requires that only one record is selected by the criteria. The function therefore returns the Excel error #NUM!.

Example 3

In the example below, the Dget function is used to find the name of the sales rep. who obtained the maximum quarterly sales figure.

	F	G
1	Sales
2	=MAX( D2:D17 )
3	=DGET( A1:D17, "Sales Rep", F1:F2 )

In the above criteria, the Max function (in cell F2) returns the maximum Sales value of $460,000. The Dget function then finds the record in row 12, that contains this value, and returns the "Sales Rep." for that record - ie. returns the text "Carol".

Further examples of the Excel Dget function can be found on the Microsoft Office website.

Dget Function Errors

If you get an error from the Excel Dget function this is likely to be one of the following :

Common Errors

#NUM!	-	Produced if more than one record in the database satisfies the supplied criteria.
#VALUE!	-	Produced if no records in the database satisfy the supplied criteria.

Excel DCOUNTA Function

Microsoft Excel DCOUNTA Function

Basic Description

The Excel Dcounta function returns the number of non-blank cells, in a field (column) of a database for selected records only.

The records to be included in the count are those that satisfy a set of one or more user-specified criteria.

The function is very similar to the Excel Dcount function, the only difference being that the Dcounta function counts all non-blank cells where the Dcount function only counts cells containing numerical values.

The syntax of the Excel Dcounta function is :

DCOUNTA( 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]	-	An optional argument which specifies the field (column) within the database for which you want to return the count of non-blank cells. 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. "Gender", "Subject", etc) If the [field] argument is omitted, the Dcounta 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 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. Gender Age Male >8

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. "Math", "Tuesday")
or
	-	an expression (eg. ">8", "<>0")

Note that 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".

Excel Dcounta Function Examples

	A	B	C	D	E
1	Name	Gender	Age	Subject	Score
2	Amy	Female	8	Math	63%
3	Amy	Female	8	English	78%
4	Amy	Female	8	Science	39%
5	Bill	Male	8	Math	55%
6	Bill	Male	8	English	71%
7	Bill	Male	8	Science	awaiting
8	Sue	Female	9	Math
9	Sue	Female	9	English	52%
10	Sue	Female	9	Science	48%
11	Tom	Male	9	Math	78%
12	Tom	Male	9	English	69%
13	Tom	Male	9	Science	65%

The following examples are based on the simple database on the right, which stores the examination marks scored by four children in three different subjects.

Example 1

The example below uses the Dcounta function to count the number of Science examination scores recorded for male students. The criteria are specified in cells G1 - H2 and the Dcounta formula is shown in cell G3:

	G	H
1	Subject	Gender
2	Science	Male
3	=DCOUNTA( A1:E13, "Score", G1:H2 )

The above Dcounta 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

Note that, in the above example, the Dcounta function has counted cell E7, in which the Score field contains the text "awaiting". (This would have been excluded from the count if the Dcount function had been used instead of the Dcounta function).

Example 2

In the example below, the Dcounta function is used to find the number of Math examination scores recorded for females.

	G	H
1	Subject	Gender
2	Math	Female
3	=DCOUNTA( A1:E13, "Score", G1:H2 )

The above Dcounta function finds two rows for which the Subject is "Math" and the Gender is "Female". However, only one of these rows has a non-blank "Score", and so the function returns the value 1

Note that, in the above two examples, instead of typing in "Score" for the Field argument, we could have simply used the number 5 (to denote the 5th column of the database).

Example 3

In the example below, the Dcounta function is used to find the number of English examination scores that are higher than 60%.

In this case, the Field argument is omitted, so the Dcounta function simply counts all rows satisfying the supplied criteria:

	G	H
1	Subject	Score
2	English	>60%
3	=DCOUNTA( A1:E13,   , G1:H2 )

The above Dcounta function finds three rows for which the Subject is "English" and the Score is greater than 60%. The function therefore returns the count 3

Excel DCOUNT Function

Microsoft Excel DCOUNT Function

Basic Description

The Excel Dcount function returns the number of cells containing numbers, in a field (column) of a database for selected records only.

The records to be included in the count are those that satisfy a set of one or more user-specified criteria.

The function is very similar to the Excel Countifs function, which was introduced as a new function in Excel 2007.

The syntax of the Excel Dcount function is :

DCOUNT( 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]	-	An optional argument which specifies the field (column) within the database for which you want to return the count. 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. "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	-	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. Subject Age Math >8

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/2010, FALSE)
or
	-	a text string (eg. "Name", "Tuesday")
or
	-	an expression (eg. ">8", "<>0")

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

Excel Dcount Function Examples

	A	B	C	D	E
1	Name	Gender	Age	Subject	Score
2	Amy	Female	8	Math	63%
3	Amy	Female	8	English	78%
4	Amy	Female	8	Science	39%
5	Bill	Male	8	Math	55%
6	Bill	Male	8	English	71%
7	Bill	Male	8	Science	awaiting
8	Sue	Female	9	Math
9	Sue	Female	9	English	52%
10	Sue	Female	9	Science	48%
11	Tom	Male	9	Math	78%
12	Tom	Male	9	English	69%
13	Tom	Male	9	Science	65%

The following examples are based on the simple database on the right, which stores the examination marks scored by four children in three different subjects.

Example 1

The example below uses the Dcount function to count the number of Science examination scores recorded for male students. The criteria are specified in cells G1 - H2 and the Dcount formula is shown in cell G3:

	G	H
1	Subject	Gender
2	Science	Male
3	=DCOUNT( A1:E13, "Score", G1:H2 )

The above Dcount function finds that there are 2 rows for which the Gender is "Male" and the Subject is "Science". However, only one of these rows (row 13) contains a number in the "Score" column. Therefore, the function returns the value 1

Note that, in the above example, the Dcount function has excluded cell E7 from the count, because this cell contains text, and not a number. (However, this cell would have been counted if the Dcounta function had been used instead of the Dcount function).

Example 2

In the example below, the Dcount function is used to find the number of Math examination scores recorded for females.

	G	H
1	Subject	Gender
2	Math	Female
3	=DCOUNT( A1:E13, "Score", G1:H2 )

The above Dcount function finds two rows in which the Subject is "Math" and the Gender is "Female". However, as cell E8 is blank, the functions only counts cell E2. Therefore the functions returns the value 1

Note that, in the above two examples, instead of typing in "Score" for the Field argument, we could have simply used the number 5 (to denote the 5th column of the database).

Example 3

In the example below, the Dcount function is used to find the number of English examination scores that are higher than 60%.

In this case, the Field argument is omitted, so the Dcount function simply counts all rows satisfying the supplied criteria:

	G	H
1	Subject	Score
2	English	>60%
3	=DCOUNT( A1:E13,   , G1:H2 )

The above Dcount function finds three rows for which the Subject is "English" and the Score is greater than 60%. The function therefore returns the count 3

Excel DAVERAGE Function

Microsoft Excel DAVERAGE Function

Basic Description

The Excel Daverage function calculates the average of values in a field (column) in a database for selected records, that satisfy user-specified criteria.

The function is very similar to the Excel Averageifs function, which was first introduced in Excel 2007.

The syntax of the Excel Daverage function is :

DAVERAGE( 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, for which the average is to be calculated. 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. "Area", "Quarter", 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. Quarter Area >2 North

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", "Wednesday")
or
	-	an expression (eg. ">2", "<>0")

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

Excel Daverage Function Examples

	A	B	C	D
1	Quarter	Area	Sales Rep.	Sales
2	1	North	Jeff	$223,000
3	1	North	Chris	$125,000
4	1	South	Carol	$456,000
5	1	South	Tina	$289,000
6	2	North	Jeff	$322,000
7	2	North	Chris	$340,000
8	2	South	Carol	$198,000
9	2	South	Tina	$222,000
10	3	North	Jeff	$310,000
11	3	North	Chris	$250,000
12	3	South	Carol	$460,000
13	3	South	Tina	$395,000
14	4	North	Jeff	$261,000
15	4	North	Chris	$389,000
16	4	South	Carol	$305,000
17	4	South	Tina	$188,000

The following examples are based on the simple database on the right, which stores the sales figures for four sales representatives, over the four quarters of a year.

Example 1

In the example below, the Daverage function is used to calculate the average quarterly sales in quarters 2, 3 and 4, for the Sales Rep. "Tina". The criteria are specified in cells F1 - G2 and the format of the Daverage function is shown in cell F3.

	F	G
1	Quarter	Sales Rep.
2	>1	Tina
3	=DAVERAGE( A1:D17, "Sales", F1:G2 )

The above Daverage function calculates the average of the values in cells D9, D13 & D17, and therefore returns the value $268,333.

Example 2

In the example below, the Daverage function is used to calculate the average sales in quarter 1, by sales reps in the "South" area.

	F	G
1	Quarter	Area
2	1	South
3	=DAVERAGE( A1:D17, "Sales", F1:G2 )

The above Daverage function calculates the average of the values in cells D4 and D5 and so returns the value $372,500.

Note that, in the above two examples, instead of typing in "Sales" for the field argument, we could have simply used the number 4 (to denote the 4th column of the database).