DSUM Function

Basic Description

The Excel Dsum function calculates the sum of a field (column) in a database for selected records, that satisfy user-specified criteria. The function is very similar to the Excel Sumifs function, which was first introduced in Excel 2007.

Syntax: DSUM( database, field, criteria )

where the arguments are:

database A range of cells containing the database. The top row of the database should specify the field names.
field

The field (column) within the database, that is to be summed.

This can either be a field number, or can be the field name (ie. the header in the top row of the database) encased in quotes (eg. “Area”, “Quarter”, etc)

criteria

A range of cells that contain the criteria, to specify which records should be included in the calculation.

The range can include one or more criteria, which are presented as a field name in one cell and the condition for that field in the cell below.

eg.

Quarter Area
>2 South

Note that the Excel database functions are not case sensitive. So, for example, the criteria =”South” will be satisfied by cells containing the text “South” or “south”.

 

Excel Dsum Function Examples

A B C D
1 Quarter Area Sales Rep. Sales
2 1 North Jafry    223,000
3 1 North Chinthia    125,000
4 1 South Chobi    456,000
5 1 South Tina    289,000
6 2 North Jafry    322,000
7 2 North Chinthia    340,000
8 2 South Chobi    198,000
9 2 South Tina    222,000
10 3 North Jafry    310,000
11 3 North Chinthia    250,000
12 3 South Chobi    460,000
13 3 South Tina    395,000
14 4 North Jafry    261,000
15 4 North Chinthia    389,000
16 4 South Chobi    305,000
17 4 South Tina    188,000

The following examples are based on the simple database on the right, which stores the sales figures for four sales representatives, working in the North or South area, over the four quarters of a year.

 

Example 1

In the example below, the Dsum function is used to calculate the total sales in quarters 3 & 4, in the “North” area. The criteria are specified in cells F1 – G2 and the Dsum formula is shown in cell F3.

F G
1 Quarter Area
2 >2 North
3 =DSUM( A1:D17, “Sales”, F1:G2 )

 

The above Dsum function calculates the sum of the values in cells D10, D11, D14 & D15, and therefore returns the value 1,210,000

 

Example 2

In the example below, the Dsum function is used to calculate the total sales in quarter 3, by sales reps with names beginning with the letter “C”. Again, the criteria are specified in cells F1 – G2 and the Dsum formula is shown in cell F3.

F G
1 Quarter Sales Rep.
2 3 C*
3 =DSUM( A1:D17, “Sales”, F1:G2 )

 

The above Dsum function sums the values in cells D11 and D12 and so returns the value 710,000. Note that, in the above two examples, instead of typing in “Sales” for the Field argument, we could have simply used the number 4 (to denote the 4th column of the database).

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment