Home Excel Access | Links | Feedback | Array functions | SUMPRODUCT() | Filtering data |

# Excel Functions: Database functions (DSum, DCount, DAverage)

These are more complex versions of the basic functions SUM, AVERAGE and COUNT. They are part of a series known as 'database functions' and they are used to interrogate lists of data held on a worksheet. For a selected table of data these functions will perform their processing only on records (or rows) which meet specified criteria. All other records are ignored.

 - A B C D E F 1 Ages of Patients seen by Doctors 2 Criteria 3 Doctor Patient Age Doctor Age 4 Dr Smith JAW 42 Dr Smith >40 5 Dr Smith TV 70 6 Dr Lam CIM 32 7 Dr Lam RJM 48 8 Mrs Dapper LV 86 9 Dr Smith RMP 16 10 11 The average age of patients seen by Dr Smith 42.67 12 The number of Dr Smiths patients aged over 40: 2.00

In this example the table is contained in the range A3:C9. Column headings must be placed directly above the data and these headings included within the range of the table in the formula.

Two different criteria have been placed in the range E3:F4. Each criteria refers to a column title and underneath it the required match. Note that the second criteria specifies patients whose Age is greater than (>) 40.

The criteria range can be expanded to include more items. If you want to include two alternatives for one variable (e.g. Dr Smith OR Dr Lam) they can be placed one below the other (in E4 and E5). In this situation, make sure that you also specify criteria for other fields against each row. If you leave the 'Age' criteria blank against a second 'Doctor', the result would be for Dr Smith's patients aged over 40 OR Dr Lam's patients for any age.

 [E11] = DAVERAGE(A3:C9, 3, E3:E4) 42.67 [E12] = DCOUNT(A3:C9,"Age", E3:F4) 2

These functions all require three arguments (each separated by commas).

1. The range of cells containing a table of data
2. The column which contains the data to be summed, averaged or counted. This item can either be the column number within the table or the column title (in inverted commas).
3. The range of cells containing one or more criteria.
 - A B C D E F 14 Length of theatre procedures 15 16 Doctor Patient Minutes 17 Dr Smith JAW 42 18 Dr Smith TV 18 19 Dr Lam CIM 34 20 Dr Lam RJM 125 21 Mrs Dapper LV 130 22 Dr Smith RMP 32 23 24 Time spent in theatres by Dr Smith: 92.00 mins

DSUM() was not included within the first example because there is no benefit in adding up patients ages. In a table containing details of time spent in operating theatres, DSUM() can be used (using the formula [E24]=DSUM(A16:C22, 3, E3:E4)) to determine the total operating time for a particular Doctor.

Click to see examples of several functions which can apply conditions to SUM calculations.

 Get this information as a documentaccompanied by Excel worksheets Click here for details about obtaining thisfile. It has been rewritten for Excel 2010.

 file: xldsumdavg.htm © meadinkent.co.uk 2015 Last updated Feb14 CMIDX S2 P1 Y