12 April 2014: The OpenOffice Wiki is not, and never was, affected by the heartbleed bug. Users' passwords are safe and wiki users do not need take any actions.

Documentation/How Tos/Calc: DSUM function

From Apache OpenOffice Wiki
Jump to: navigation, search


DSUM

Sums the cells in a column of a Calc 'database' table, in rows which meet specified criteria.


Syntax:

DSUM(database_table; field; criteria_table)

where

database_table is a range defining the data to be processed.
field is the column to sum. It may be a column number (1 is the first column of the database table, 2 is the second ...) or a column header (enclosed in quotation marks ””) or a cell referring to a column header.
criteria_table is a range containing criteria, which are used to select which rows of the database_table to sum.


The Database functions overview fully describes the use of these parameters.


Example:

In this spreadsheet:

  A B C D E
1 Name Grade Age Distance to School Weight
2 Andy 3 9 150 40
3 Betty 4 10 1000 42
4 Charles 3 10 300 51
5 Daniel 5 11 1200 48
6 Eva 2 8 650 33
7 Frank 2 7 300 42
8 Greta 1 7 200 36
9 Harry 3 9 1200 44
10 Irene 2 8 1000 42
11          
12          
13 Name Grade Age Distance to School Weight
14   2      


DSUM(A1:E10; "Distance to School"; A13:E14)

returns the combined distance to school of all children who are in the second grade (1950).

DSUM(A1:E10; 4; A13:E14)

returns the same result.

DSUM(A1:E10; D1; A13:E14)

also returns the same result.

Similar

Using Arrays shows a similar technique:

  A B C
1 Job Ref Employee Cost
2 1 Andy £30
3 2 Betty £40
4 3 Charles £30
5 3 Daniel £50
6 4 Eva £20

To find the total cost where Job Ref = 3 (ie £80)

SUM( (A2:A6 = 3) * C2:C6 )

will SUM() values in C2:C6 where the corresponding value in A2:A6 is = 3. Note that this uses the special array expression mechanism and must be entered by pressing Ctrl-Shift-Enter.

Issues:

The OOo2.3 Help implies that 0 as a field will include the entire database table. This seems to be wrong - only a single column can be summed.


See Also

Retrieved from "https://wiki.openoffice.org/w/index.php?title=Documentation/How_Tos/Calc:_DSUM_function&oldid=219931"
Views
Personal tools
Navigation
Print/export
Tools