Difference between revisions of "Documentation/How Tos/Calc: DSUM function"

From Apache OpenOffice Wiki
Jump to: navigation, search
m (Robot: Automated text replacement %s)
(Added link to similar approach using array expression)
Line 84: Line 84:
  
 
: also returns the same result.
 
: also returns the same result.
 +
 +
=== Similar ===
 +
 +
[[Documentation/How_Tos/Using Arrays#Sum_of_entries_matching_multiple_conditions|Using Arrays]] shows a similar technique:
 +
 +
{| border="1"
 +
|-align="center"
 +
| ||'''A'''||'''B'''||'''C'''
 +
 +
|-align="center"
 +
|'''1'''||Job Ref||Employee||Cost
 +
 +
|-align="center"
 +
|'''2'''||1||Andy||£30
 +
 +
|-align="center"
 +
|'''3'''||2||Betty||£40
 +
 +
|-align="center"
 +
|'''4'''||3||Charles||£30
 +
 +
|-align="center"
 +
|'''5'''||3||Daniel||£50
 +
 +
|-align="center"
 +
|'''6'''||4||Eva||£20
 +
|}
 +
 +
To find the total cost where Job Ref = 3 (ie £80)
 +
 +
<tt>'''SUM( (A2:A6 = 3) * C2:C6 )'''</tt>
 +
 +
: 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.
  
  

Revision as of 15:16, 27 February 2009


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.


Template:Documentation/SeeAlso

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.

Retrieved from "https://wiki.openoffice.org/w/index.php?title=Documentation/How_Tos/Calc:_DSUM_function&oldid=113983"
Personal tools