Would you like to learn more?
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.
|1||Ages of Patients seen by Doctors|
|4||Dr Smith||JAW||42||Dr Smith||>40|
|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.
These functions all require three arguments (each separated by commas).
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.
|14||Length of theatre procedures|
|24||Time spent in theatres by Dr Smith:||92.00||mins|
|Get this information as a document
accompanied by Excel worksheets
|Click here for details about
file. It has been rewritten for Excel 2010.
|file: xldsumdavg.htm||© meadinkent.co.uk 2013||Last updated Nov11||CMIDX S2 P1 Y|