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

From Apache OpenOffice Wiki
Jump to: navigation, search
m
m (→‎Example:)
 
(13 intermediate revisions by 8 users not shown)
Line 1: Line 1:
−
__NOTOC__
+
{{DISPLAYTITLE: DSUM function}}
 +
{{Documentation/CalcFunc DatabaseTOC
 +
|ShowPrevNext=block
 +
|PrevPage=Documentation/How_Tos/Calc:_DSTDEV_function
 +
|NextPage=Documentation/How_Tos/Calc:_DVAR_function
 +
}}__NOTOC__
  
 
== DSUM  ==
 
== DSUM  ==
Line 17: Line 22:
  
  
−
The '''[[Documentation/How_Tos/Calc: Database functions|Database functions overview]]''' fully describes the use of these parameters.
+
The [[Documentation/How_Tos/Calc: Database functions|Database functions overview]] fully describes the use of these parameters.
  
  
Line 24: Line 29:
 
In this spreadsheet:
 
In this spreadsheet:
  
−
{| border="1"  
+
{| border="1"
 
|-align="center"
 
|-align="center"
−
| ||'''A'''||B'''||C'''||'''D'''||'''E'''
+
| ||'''A'''||'''B'''||'''C'''||'''D'''||'''E'''
−
 
+
 
|-align="center"
 
|-align="center"
 
|'''1'''||Name||Grade||Age||Distance to School||Weight
 
|'''1'''||Name||Grade||Age||Distance to School||Weight
−
 
 
|-align="center"
 
|-align="center"
 
|'''2'''||Andy||3||9||150||40
 
|'''2'''||Andy||3||9||150||40
−
 
 
|-align="center"
 
|-align="center"
 
|'''3'''||Betty||4||10||1000||42
 
|'''3'''||Betty||4||10||1000||42
−
 
 
|-align="center"
 
|-align="center"
 
|'''4'''||Charles||3||10||300||51
 
|'''4'''||Charles||3||10||300||51
−
 
 
|-align="center"
 
|-align="center"
 
|'''5'''||Daniel||5||11||1200||48
 
|'''5'''||Daniel||5||11||1200||48
−
 
 
|-align="center"
 
|-align="center"
 
|'''6'''||Eva||2||8||650||33
 
|'''6'''||Eva||2||8||650||33
−
 
 
|-align="center"
 
|-align="center"
 
|'''7'''||Frank||2||7||300||42
 
|'''7'''||Frank||2||7||300||42
−
 
 
|-align="center"
 
|-align="center"
 
|'''8'''||Greta||1||7||200||36
 
|'''8'''||Greta||1||7||200||36
−
 
 
|-align="center"
 
|-align="center"
 
|'''9'''||Harry||3||9||1200||44
 
|'''9'''||Harry||3||9||1200||44
−
 
 
|-align="center"
 
|-align="center"
 
|'''10'''||Irene||2||8||1000||42
 
|'''10'''||Irene||2||8||1000||42
−
 
 
|-align="center"
 
|-align="center"
 
|'''11'''|| || || || || 
 
|'''11'''|| || || || || 
−
 
 
|-align="center"
 
|-align="center"
 
|'''12'''|| || || || || 
 
|'''12'''|| || || || || 
−
 
 
|-align="center"
 
|-align="center"
 
|'''13'''||Name||Grade||Age||Distance to School||Weight
 
|'''13'''||Name||Grade||Age||Distance to School||Weight
−
 
 
|-align="center"
 
|-align="center"
 
|'''14'''|| ||2|| || || 
 
|'''14'''|| ||2|| || || 
−
 
 
|}
 
|}
−
 
  
 
<tt>'''DSUM(A1:E10; "Distance to School"; A13:E14)'''</tt>
 
<tt>'''DSUM(A1:E10; "Distance to School"; A13:E14)'''</tt>
Line 85: Line 74:
 
: also returns the same result.
 
: also returns the same result.
  
 +
=== Similar ===
  
−
=== See also: ===
+
[[Documentation/How_Tos/Using Arrays#Sum_of_entries_matching_multiple_conditions|Using Arrays]] shows a similar technique:
−
'''[[Documentation/How_Tos/Calc: Database functions|Database functions overview]]'''
+
  
−
'''[[Documentation/How_Tos/Calc: DCOUNT function|DCOUNT function]]''',
+
{| border="1"
−
'''[[Documentation/How_Tos/Calc: DCOUNTA function|DCOUNTA function]]''',
+
|-align="center"
−
'''[[Documentation/How_Tos/Calc: DPRODUCT function|DPRODUCT function]]''',
+
|&nbsp;||'''A'''||'''B'''||'''C'''
  
−
'''[[Documentation/How_Tos/Calc: DMAX function|DMAX function]]''',
+
|-align="center"
−
'''[[Documentation/How_Tos/Calc: DMIN function|DMIN function]]''',
+
|'''1'''||Job Ref||Employee||Cost
−
'''[[Documentation/How_Tos/Calc: DAVERAGE function|DAVERAGE function]]''',
+
  
−
'''[[Documentation/How_Tos/Calc: DSTDEV function|DSTDEV function]]''',
+
|-align="center"
−
'''[[Documentation/How_Tos/Calc: DSTDEVP function|DSTDEVP function]]''',
+
|'''2'''||1||Andy||£30
−
'''[[Documentation/How_Tos/Calc: DVAR function|DVAR function]]''',
+
−
'''[[Documentation/How_Tos/Calc: DVARP function|DVARP function]]''',
+
  
−
'''[[Documentation/How_Tos/Calc: DGET function|DGET function]]'''
+
|-align="center"
 +
|'''3'''||2||Betty||£40
  
−
'''[[Documentation/How_Tos/Calc: SUM function|SUM function]]''',
+
|-align="center"
−
'''[[Documentation/How_Tos/Calc: SUMIF function|SUMIF function]]''',
+
|'''4'''||3||Charles||£30
−
'''[[Documentation/How_Tos/Calc: SUBTOTAL function|SUBTOTAL function]]'''
+
  
−
'''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]'''
+
|-align="center"
 +
|'''5'''||3||Daniel||£50
  
 +
|-align="center"
 +
|'''6'''||4||Eva||£20
 +
|}
  
−
==Issues==
+
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.
 +
 +
===Issues:===
 
The OOo2.3 Help implies that <tt>'''0'''</tt> as a <tt>'''field'''</tt> will include the entire database table. This seems to be wrong - only a single column can be summed.
 
The OOo2.3 Help implies that <tt>'''0'''</tt> as a <tt>'''field'''</tt> will include the entire database table. This seems to be wrong - only a single column can be summed.
 +
 +
{{SeeAlso|EN|
 +
* [[Documentation/How_Tos/Calc: DCOUNT function|DCOUNT]]
 +
* [[Documentation/How_Tos/Calc: DCOUNTA function|DCOUNTA]]
 +
* [[Documentation/How_Tos/Calc: DPRODUCT function|DPRODUCT]]
 +
* [[Documentation/How_Tos/Calc: DMAX function|DMAX]]
 +
* [[Documentation/How_Tos/Calc: DMIN function|DMIN]]
 +
* [[Documentation/How_Tos/Calc: DAVERAGE function|DAVERAGE]]
 +
* [[Documentation/How_Tos/Calc: DSTDEV function|DSTDEV]]
 +
* [[Documentation/How_Tos/Calc: DSTDEVP function|DSTDEVP]]
 +
* [[Documentation/How_Tos/Calc: DVAR function|DVAR]]
 +
* [[Documentation/How_Tos/Calc: DVARP function|DVARP]]
 +
* [[Documentation/How_Tos/Calc: DGET function|DGET]]
 +
* [[Documentation/How_Tos/Calc: SUM function|SUM]]
 +
* [[Documentation/How_Tos/Calc: SUMIF function|SUMIF]]
 +
* [[Documentation/How_Tos/Calc: SUBTOTAL function|SUBTOTAL]]
 +
* [[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]
 +
* [[Documentation/How_Tos/Calc: Database functions#Overview|Database functions overview]]
 +
* [[Documentation/How_Tos/Calc: Database functions|Database functions]]
 +
* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
 +
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
 +
[[Category: Documentation/Reference/Calc/Database functions]]

Latest revision as of 13:16, 30 January 2024



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=259674"
Views
Personal tools
Navigation
Print/export
Tools