Once you've built up your skills in using Excel you may have come across variations of the same function such as SUM and DSUM. This article reveals what the D in DSUM means and describes how it can be used in practice. You'll then be able to go on and use other similar functions such as DAVERAGE, DMAX, DMIN and more.

The D in DSUM stands for Database and all functions like this are used with a table of cells rather than with just a single row or column. In the table of cells you can choose which heading to SUM. However DSUM is special because you can also apply criteria to the SUM based on these same table headings. This is what makes DSUM so useful.

Suppose you have a table of data in Excel with the first row as headings. If you're interested in summing then one or more of the table columns will contain numerical data. You can of course just SUM a chosen numerical column and be done with it. However we can use DSUM to SUM the same column but use with criteria to carry out a selective SUM.

You can setup a separate table of criteria by copying the original table headings and then pasting them to a different part of the worksheet. Then under the headings in this other table you just type in the required criteria. You can enter text such as London or Manager or Blue under text headings which contain similar text data under the original table headings. You can also enter data such as 50 or >50 or <50 under numerical headings which contain similar numerical data under the original table headings.

If you're working with ranges of cells in Excel formula it can be very useful to give the cells in question a range name. Then you can refer to the range name in the formula, saving you the need to drag the cells in question as you build the formula. To do this you highlight the required cells, for example the original data cells and heading, click in the white cell locator box immediately above and to the far left of the worksheet, and then type in a range name such as STAFF or SALES or STOCK, or any name you like, then press the Enter key. Note that you cannot use spaces in range names. Then do the same for the criteria table.

Select the criteria headings and the row containing the criteria values immediately under the headings. Give the range a name, such as CRITERIA, and press the enter key. Suppose we've named the original data table STAFF and the criteria table CRITERIA. Also suppose there are columns labelled SALARY, TOWN and POST in the original staff table.

We decide to DSUM the SALARY data subject to the criteria in the criteria table, which contains London under the TOWN heading. To create the DSUM formula we first select an empty cell then type =DSUM(STAFF,SALARY,CRITERIA) and press the Enter key. The cell will show the total salary for all the staff for London. You can try changing the criteria values in the criteria table, and the DSUM formula will update to meet the new criteria.

If you enter more than one criteria value in the criteria table, in the same row, the criteria are additive. For example if the criteria contains LONDON under the TOWN heading and MANAGER under the POST heading, then DSUM will show the total salaries for all records for London Managers. DSUM has summed the salaries from the main table for records which meet both criteria in the criteria table.

You can also become more ambitious and add more criteria in successive cells under the same criteria heading. However if you do this, you'll need to edit the CRITERIA named change to take account of the extra criteria rows. If you use more than one criteria in different rows, the criteria are regarded as OR by DSUM. So if you enter LONDON, then MANCHESTER in successive cells in the criteria table under the TOWN criteria heading, and don't forget to alter the criteria name range if necessary, then DSUM will add the salaries for all records in the original table for staff based in London or Manchester.

So DSUM is unique because you can SUM use multiple criteria just like a database, and hence the D in the formula name. So if you're next Training Needs Analysis identifies a requirement for database training in Excel, then DSUM is your starting point. You'll find lots more Excel functions beginning with D and this means they can all use multiple criteria.