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

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.


PDF Excel functions guide
formulas used in example
[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.



excel Get this information as a document
accompanied by Excel worksheets		 Document is in PDF format Click here for details about obtaining this
file. It has been rewritten for Excel 2010.
file: xldsumdavg.htm © meadinkent.co.uk 2015 Last updated Feb14 CMIDX S2 P1 Y