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

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

Would you like to learn more?

Excel for dummies 2010

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. Note that column headings must be placed directly above the data and these headings included within the range of the table.

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. Any number of valid criteria in a single range of cells can be used as an argument to these functions.


formulae 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.

It would be possible to include Dr Lam as well as Dr Smith simply by entering his name in cell E5 (underneath Dr Smith). The formula can then be amended by increasing the range of the criteria (i.e. E3:F5). Note that it would also be necessary to enter >40 in cell F5 or the function will pick up all of Dr Smith's patients over 40 and all of Dr Lam's patients regardless of age.

DSUM() has not been 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.

- 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

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 2013 Last updated Nov11 CMIDX S2 P1 Y